Important read this :
The code on this page is only working when you use Outlook as your mail
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
The easiest way is to add this code line that change the sender name and reply address in the Outlook macro examples on my site.
'The receiver can see the original mail address in the properties if he
.SentOnBehalfOfName = """SenderName"" <Reply@Address.com>"
If you want to mail from another account then your default mail account
in Outlook 2007-2013
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
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.
Sub Mail_small_Text_Change_Account() 'Only working in Office 2007-2013 'Don't forget to set a reference to Outlook in the VBA editor 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 = "firstname.lastname@example.org" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = strbody 'SendUsingAccount is new in Office 2007 'Change Item(1)to the account number that you want to use .SendUsingAccount = OutApp.Session.Accounts.Item(1) .Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub