VBA Outlook send mail automatically by specified outlook mail box

stephen li

New Member
Outlook version
Outlook 2016 64 bit
Email Account
Exchange Server 2013
I try to make a Marco to help our HR to send birthday mail to our colleagues automatically by recording there information into an excel file.

The Marco as below:
Sub fa()

On Error Resume Next
Dim rowCount, endRowNo, today
Dim objOutlook As New Outlook.Application
Dim objMail As MailItem

endRowNo = Cells(1, 1).CurrentRegion.Rows.Count
today = Mid(Cells(2, 7).Value, 1, 4)

Set objOutlook = New Outlook.Application

For rowCount = 2 To endRowNo

If today = Mid(Cells(rowCount, 6).Value, 1, 4) Then

Set objMail = objOutlook.CreateItem(olMailItem)
With objMail

.To = Cells(rowCount, 1).Value

.Subject = Cells(rowCount, 2).Value

.HTMLBody = (Cells(rowCount, 3).Value & Cells(rowCount, 4).Value)

End With

Set objMail = Nothing

End If


Set objOutlook = Nothing

End Sub

My question is for the below Marco, the mail will be sent by the outlook account which the system account.
How can I change the sender to other outlook account by setting the account name and password?

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
You need to add the account to the Outlook profile. All added accounts are listed in Session.Accounts. Pick one and assign it to the objMail.SendUsingAccount property.

Or, if you have an Exchange server, set the email address to the objMail.SentOnBehalfOfName property. This will use your default account but appears for the receiver as it was sent from the other address.