Kevin Judge
asked on
Excel VBA Macro to send email from a specific Outlook account
I have a macro that sends emails. I need to change the email account that the email is sent FROM. I have tried the SendOnBehalfOfName and SendUsingAccount parameters but can't get it to work. I can only get it to send from my default Outlook email account, please advise! NB: I am using Excel and Outlook 2007
Dim OutApp As Object
Dim OutMail As Object
Dim strfrom As String, strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Appl ication")
Set OutMail = OutApp.CreateItem(0)
strfrom = "my@email.com"
strto = "you@email.com"
strcc = ""
strbcc = ""
strsub = "Body Text"
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.HTMLBody = strbody
.Send
End With
Dim OutApp As Object
Dim OutMail As Object
Dim strfrom As String, strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Appl
Set OutMail = OutApp.CreateItem(0)
strfrom = "my@email.com"
strto = "you@email.com"
strcc = ""
strbcc = ""
strsub = "Body Text"
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.HTMLBody = strbody
.Send
End With
ASKER
Hi
The loop through outlook accounts works, however, it crashes on the ".SendUsingAccount = oAccount" line regardless of oAccount being set to default or the account I want!! FYI the outlook email accounts are IMAP.
The loop through outlook accounts works, however, it crashes on the ".SendUsingAccount = oAccount" line regardless of oAccount being set to default or the account I want!! FYI the outlook email accounts are IMAP.
maybe try
Dim OutApp As Object
Dim OutMail As Object
Dim oAccount As Object
Dim strfrom As String, strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strfrom = "my@email.com"
strto = "you@email.com"
strcc = ""
strbcc = ""
strsub = "Body Text"
For Each oAccount In OutApp.Session.Accounts
If oAccount.DisplayName = strfrom Then 'OR oAccount.SmtpAddress = strfrom
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.HTMLBody = strbody
Set .SendUsingAccount = oAccount
.Send
End With
End If
Next
ASKER
"Set .SendUsingAccount = oAccount" did the trick! However, I have many different emails to send, so do I have to do this loop for each email? Can't I just hard code Set .SendUsingAccount = "emal@domain.com" and avoid repeating the code searching for the email account!?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes that works!! So FYI I have stripped out all the other code as it is surplus to requirement and have inserted Set .SendUsingAccount = OutApp.Session.Accounts.It em("emal@d omain.com" ) command before each .send line!
Many thanks for your help and prompt replies, excellebt service.
PS I am new to Experts Exchange; what do I do next?
Many thanks for your help and prompt replies, excellebt service.
PS I am new to Experts Exchange; what do I do next?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic, thank you!
pls try
Open in new window
Regards