VBA Outlook send mail automatically by specified outlook mail box

Status
Not open for further replies.

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)

.Send
End With

Set objMail = Nothing

End If

Next

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.
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
N VBA Script to Send Automatic Emails from Outlook 2010 Outlook VBA and Custom Forms 1
D Outlook VBA to open Excel attachment and send recipient's email address to a workbook cell? Using Outlook 4
H Problems With Outlook 2013 VBA To Send and Print an email Outlook VBA and Custom Forms 1
M VBA Send Sales reports using .oft files, originate in Outlook or Excel? Using Outlook 5
A send email from Access via Outlook (VBA) Using Outlook 1
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
N How can I increase/faster outlook VBA Macro Speed ? Using Outlook 2
N Outlook Email Rule execution through shortcut keys (VBA codes) Using Outlook 1
A VBA Code in Outlook disappears after first use Outlook VBA and Custom Forms 1
dweller Outlook 2010 Rule Ignores VBA Script Outlook VBA and Custom Forms 2
G Outlook VBA and Google Calendar ("Events") Outlook VBA and Custom Forms 1
J VBA Outlook : Subject line : Cut and Paste name to heading , number to very end of the body of Email Outlook VBA and Custom Forms 1
B Advanced Search in MS Outlook by VBA and SQL Outlook VBA and Custom Forms 2
K Outlook Archive to PST Files by Date Range VBA Script? Outlook VBA and Custom Forms 1
J Help Please!!! Outlook 2016 - VBA Macro for replying with attachment in meeting invite Outlook VBA and Custom Forms 9
S Find a cell value in excel using outlook vba Using Outlook 1
J Execute Add-In Button from VBA Outlook 2016 Outlook VBA and Custom Forms 1
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
M Slow VBA macro in Outlook Outlook VBA and Custom Forms 5
T Outlook AntiSpam with VBA Outlook VBA and Custom Forms 1
F "Move to" O365 feature to Outlook client via VBA Outlook VBA and Custom Forms 4
B query outlook using vba Outlook VBA and Custom Forms 13
J VBA to switch Outlook online/offline Outlook VBA and Custom Forms 4
M VBA to change flag status in outlook contact item Outlook VBA and Custom Forms 3
T VBA outlook, detect priority emails Outlook VBA and Custom Forms 5
C Need VBA code to automatically save message outside outlook and add date Outlook VBA and Custom Forms 1
S Outlook VBA Contacts Notes Outlook VBA and Custom Forms 0
O VBA to Run Font Change on Outlook Startup Outlook VBA and Custom Forms 4
P Outlook 2007 Email Categorization using VBA Outlook VBA and Custom Forms 1
O VBA or other solution for Outlook tasks to OneNote Outlook VBA and Custom Forms 0
S Automatically selecting folders and deleting messages in Outlook VBA Outlook VBA and Custom Forms 7
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1
Diane Poremsky Outlook VBA: Use a Text File to Populate a ListBox New Slipstick.com Articles 0
C Saving Outlook attachments and links to attachments with VBA Outlook VBA and Custom Forms 2
Diane Poremsky Outlook VBA: Work with Open Item or Selected Item New Slipstick.com Articles 0
Diane Poremsky Working with VBA and non-default Outlook Folders New Slipstick.com Articles 0
C Outlook VBA to set current account Outlook VBA and Custom Forms 1
Diane Poremsky Use VBA to create an Outlook Search Folder for Sender New Slipstick.com Articles 0
F VBA routine to write new sub routine in outlook Outlook VBA and Custom Forms 0
D Change sender name outlook vba 2010 Custom Userform Outlook VBA and Custom Forms 1
G Adding a contact to Outlook with a custom form using Access VBA Outlook VBA and Custom Forms 1
C Outlook 2016 - converting Word VBA to default Outlook message Outlook VBA and Custom Forms 0
P Outlook 2016 from Excel 2016 VBA Using Outlook 1
N Get the seconds with userproperties.add method VBA Outlook Outlook VBA and Custom Forms 4
S Outlook 7 VBA macro for multiple filing Outlook VBA and Custom Forms 1
G Outlook 2010 VBA Coding Assistance please! Outlook VBA and Custom Forms 5
Similar threads


















































Top