Use the Account you want in mail macro in Excel/Outlook 2007-2010
Ron de Bruin (last updated 20-Feb-2010)
Go back to the Mail index page
Important read this :

The code on this page is only working with Outlook and not with Outlook Express or Windows Mail.
Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
http://www.rondebruin.nl/code.htm

Then Add a reference to the Microsoft outlook Library

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
    ? is the Excel version number


Example

If you want to mail from another account then your default mail account in Outlook 2007-2010
then you can use SendUsingAccount, this is added to the object model in Outlook 2007.

First we must know the account number that we want to use in the macro.
Run the macro below so you know the number that you must use in the mail macro.
Sub Which_Account_Number()
    Dim OutApp As Outlook.Application
    Dim I As Long

    Set OutApp = CreateObject("Outlook.Application")

    For I = 1 To OutApp.Session.Accounts.Count
        MsgBox OutApp.Session.Accounts.Item(I) & " : This is account number " & I
    Next I

End Sub

Now you know the number of the account that you want to use in your mail macro.
The following test subroutine sends a small text in an e-mail message.
Change the mail address and the Item number "Item(1)" in the macro before you run it.
Sub Mail_small_Text_Change_Account()
'Is only working in Office 2007-2010
'You must add a reference to the Microsoft Outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody

        'SendUsingAccount is new in Outlook 2007
        'Change Item(1)to another number to use another account
        .SendUsingAccount = OutApp.Session.Accounts.Item(1)

        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

If it is working Ok for you can use it in all the Outlook examples on my mail page.
http://www.rondebruin.nl/sendmail.htm

If you have problems with this example let me know