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.Application")
    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
Kevin JudgeAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls 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
                .SendUsingAccount = oAccount
                .Send
            End With
        End If
    Next

Open in new window

Regards
0
 
Kevin JudgeAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rgonzo1971Commented:
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

Open in new window

0
 
Kevin JudgeAuthor Commented:
"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!?
0
 
Rgonzo1971Commented:
you could try ( not sure it works)

      Set .SendUsingAccount = OutApp.Session.Accounts.Item("emal@domain.com")

Regards
1
 
Kevin JudgeAuthor Commented:
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.Item("emal@domain.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?
0
 
Kevin JudgeAuthor Commented:
Fantastic, thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.