Use the mail account you want in your mail macro

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Important read this :

The code on this page is only working when you use Outlook as your mail program.
Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet

Use SendUsingAccount in Excel/Outlook 2007 or higher

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

First add a reference to the Microsoft Outlook Library in your Excel workbook

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 Outlook version number

First we must know the account number that we want to use.
Run the macro below so you know the account number that you must use in the mail macro.

Sub Which_Account_Number()
'Don't forget to set a reference to Outlook in the VBA editor
    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 you can use the following test subroutine that send 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, you can also use the account name(see comment in the code)

Sub Mail_small_Text_Change_Account()
'Only working in Office 2007 and higher
'Don't forget to set a reference to Outlook in the VBA editor
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim OutAccount As Outlook.Account
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    'Use the first account, see that Item is 1 now
    Set OutAccount = OutApp.Session.Accounts.Item(1)
    
    'Or us the name instead of the number
    'Set OutAccount = OutApp.Session.Accounts("ron@something.nl")
    
    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 = OutAccount
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    Set OutAccount = Nothing
End Sub

Note : For many more mail examples with Outlook see this page :

Mail from Excel with Outlook (Windows)

SentOnBehalfOfName

Use SentOnBehalfOfName as long as your Exchange account has SendAs permission for the shared mailbox or distribution group

.SentOnBehalfOfName = "Sales@something.nl"

Ron de BruinExcel Automation