How to refer to non-default account?

Question

I am trying to send with another account however the VBA defaults to the main email.

I want to use no_reply mailbox however it uses firstname.lastname@company.com.

I even changed the no_reply to my default email by going into account settings in Outlook.

I checked while running the code if it is referring to the no_reply when it creates a new mail window, and it does at line Set OutAccount = myMail.Session.Accounts.Item(1) which shows as no_reply. However the email message shows first.last@company.com.

Sub Send_EmailV21()

Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Dim lastrow As Long
Dim i As Integer
Dim Sheet As Worksheet
Dim OutAccount As Outlook.Account

Application.ScreenUpdating = False

On Error Resume Next

lastrow = ThisWorkbook.Worksheets("Sheet1").Range("A1").End(xlDown).Row

For i = 2 To lastrow
    'If ThisWorkbook.Worksheets("Sheet2").Range("T" & i) = "No" Then
    
    Set outlookApp = New Outlook.Application
    Set myMail = outlookApp.CreateItem(olMailItem)
    'Set OutAccount = myMail.Session.Accounts.Item(1)
    source_file = ThisWorkbook.Worksheets("Sheet1").Range("E" & i).Value
    source_file2 = ThisWorkbook.Worksheets("Sheet1").Range("F" & i).Value
    
    Set Sheet = ThisWorkbook.Worksheets("Sheet1")
    
    myMail.Attachments.Add source_file
    myMail.Attachments.Add source_file2
    'Set myMail.SendUsingAccount = myMail.Session.Accounts.Item(1)
    myMail.To = ThisWorkbook.Worksheets("Sheet1").Range("D" & i).Value
    myMail.Subject = "Subject Line"
    myMail.HTMLBody = "whatever i want to write in the email"
    
    myMail.Display
    myMail.Send
    
    ThisWorkbook.Worksheets("Sheet1").Range("G" & i) = "Yes"
    'Else
    'End If
    
    Application.ScreenUpdating = True
 
Next i

End Sub

Adding the following line worked.

myMail.SentOnBehalfOfName = "blah@company.com"

Is that a separate POP3/SMTP or a delegate Exchange mailbox? — Dmitry Streblechenko, May 18 '21 at 00:48
No i believe it falls under the same SMTP @DmitryStreblechenko — David D, May 18 '21 at 00:56
Does this answer your question? [How to send email from a specific Outlook account using Excel?](https://stackoverflow.com/questions/55360867/how-to-send-email-from-a-specific-outlook-account-using-excel) — niton, May 18 '21 at 02:19

score 0 · Answer 1 · answered May 18 '21 at 20:49

I'd suggest iterating over all accounts configured in the profile and choose the required one. By using indexes you may choose a wrong account mistakenly.

' Loop over the Accounts collection of the current Outlook session.
Dim accounts As Outlook.Accounts = application.Session.Accounts
Dim account As Outlook.Account
For Each account In accounts
   ' When the email address matches, return the account.
   If account.SmtpAddress = smtpAddress Then
      Return account
   End If
Next

See Send an email given the SMTP address of an account for more information.

How to refer to non-default account?

1 Answers1