Solved

Excel VBA Macro to send email from a specific Outlook account

Posted on 2014-12-28
8
2,127 Views
Last Modified: 2014-12-29
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
0
Comment
Question by:Kevin Judge
  • 4
  • 4
8 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40521408
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
 

Author Comment

by:Kevin Judge
ID: 40521694
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
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40521715
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:Kevin Judge
ID: 40521742
"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
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 40521758
you could try ( not sure it works)

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

Regards
0
 

Author Comment

by:Kevin Judge
ID: 40521910
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
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40521923
0
 

Author Closing Comment

by:Kevin Judge
ID: 40521960
Fantastic, thank you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Re-position the objects 7 117
vbModal 12 57
Copy a range from 1..n excel sheets to one destination sheet 2 57
Powerpoint 2013: Change cell reference in excel link 3 84
The purpose of this article is to demonstrate how we can use conditional statements using Python.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question