How to send email from a specific Outlook account using Excel?

Question

I have code that sends emails using the default Outlook account.

I tried changing the code to send from a specific email. When I run the macro, nothing happens.

Is something wrong with the code, or is it not working due to another issue (with Outlook and the accounts/permissions associated with it)?

Sub CommandButton1_Click()

Dim wb As Workbook
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim q As Long

Dim oAccount As Outlook.Account

Set wb = ThisWorkbook

For Each oAccount In Outlook.Application.Session.Accounts

    If oAccount = "theEmailiWantToUse@domain.com" Then

        For q = 2 To 3 'LastRow

            eName = wb.Sheets(1).Cells(q, 2).Value

            Set olApp = New Outlook.Application
            Set olMail = olApp.CreateItem(olMailItem)

            mailBody = "Hello, "

            With olMail
                .To = Worksheets("Emails").Cells(q, 4).Value
                .Subject = eName
                .HTMLBody = "<!DOCTYPE html><html><head><style>"
                .HTMLBody = .HTMLBody & "body{font-family: Calibri, ""Times New Roman"", sans-serif; font-size: 14px}"
                .HTMLBody = .HTMLBody & "</style></head><body>"
                .HTMLBody = .HTMLBody & mailBody & "</body></html>"
         
                Set .SendUsingAccount = oAccount
                .Display
                ' .Send
            End With
    
        Next

    Else
    End If
 
Next

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

I know I have access to the email I would like to send emails from, as I can select it from Outlook and it works.

When Display shows the menage, do you see the right account selected? Is oAccount an Exchange or a POP3?IMAP4/SMTP account? — Dmitry Streblechenko, Mar 26 '19 at 15:53
Now nothing happens at all (nothing gets displayed)-- assuming that something is wrong with the "If oAccount.." line-- and it is an Exchange account-- I don't know what the difference would be in the code though. — Tester_Y, Mar 26 '19 at 15:59
You should not compare Account object wit ha string. Use either SmtpAddress or DisplayName property — Dmitry Streblechenko, Mar 26 '19 at 17:29

score 3 · Answer 1 · answered Mar 26 '19 at 15:31

3

Add this line within the olMail

    .SentOnBehalfOfName = "youraddress" 'here change this

answered Mar 26 '19 at 15:31

Damian

5,152
1
10
21

That works without having to go through the whole 'oAccount' method, however due to work permissions, I do not have access to "send on behalf of" the required domain. – Tester_Y Mar 26 '19 at 15:35
1

Hmmm does [this](https://stackoverflow.com/questions/49295235/choose-account-in-outlook-when-send-mail-via-excel-vba) help? – Damian Mar 26 '19 at 15:39
Unfortunately not-- I do have "Set" before the .SendUsingAccount. The issue is the macro now does nothing, so my best guess is that the If function is not being triggered to process the rest of the code, but I do not know why. – Tester_Y Mar 26 '19 at 15:41

skkakkar · Accepted Answer · 2019-03-26T16:21:28.520

please use this routine to find Account number of sender .

Sub Which_Account_Number()
'Don't forget to set a reference to Outlook in the VBA editor
    Dim OutApp As Outlook.Application
    Dim I As Long

    Set OutApp = CreateObject("Outlook.Application")

    For I = 1 To OutApp.Session.Accounts.Count
        MsgBox OutApp.Session.Accounts.Item(I) & " : This is account number " & I
    Next I
End Sub

Then

   .SendUsingAccount = olApp.Session.Accounts.Item(5)' whatever account index number you want to send. i have chosen 5

instead of

Set .SendUsingAccount = oAccount

This method works for me . You can further integrate this concept in your programme. Please ensure Reference to Outlook Object Library is set in Tools/References.

Awesome, thank you! Worked perfectly (and I actually understand why now too lol). — Tester_Y, Mar 26 '19 at 17:03

How to send email from a specific Outlook account using Excel?

2 Answers2

Linked