Excel VBA - Send email in Outlook using 2nd account

[trimShady]

Hello, I'm new to VBA. I have a working macro below to send Outlook mail using Excel VBA:

Sub Use_2nd_Account()

Dim outlook As outlook.Application
Dim outlookmail As outlook.mailItem

Set outlook = New outlook.Application
Set outlookmail = outlook.CreateItem(olMailItem)

With outlookmail
    .To = Sheet1.Cells(2, 1).Value
    .CC = ""
    .Subject = Sheet1.Cells(2, 2).Value
    .Attachments.Add ActiveWorkbook.path & "/attachment1.png"

    .HTMLBody = "hello world!" 
     
     'Set .SendUsingAccount = .Session.Accounts.Item(2)  
    
    .Display
    
End With

End Sub

I'd like to be able to send email using a 2nd account but I am having trouble getting the "Set .SendUsingAccount" to work. I'm using Office365. Any help would be greatly appreciated. Thanks in advance.

 

[6StringJazzer]

I use this method successfully. I don't know if it's the best, but it works. I do this in Outlook, have not tested in Excel.

By the way, it's not a good practice to use "outlook" as a variable name.

Sub Use_2nd_Account()

Dim outlook As outlook.Application 
Dim outlookmail As outlook.mailItem

Set outlook = New outlook.Application

Dim oAccount As Outlook.Account

For Each oAccount In outlook.Session.Accounts
   If oAccount = "name_of_account" Then ' the account name is usually an email address
      Set outlookmail = outlook.CreateItem(olMailItem)
      outlookmail.SendUsingAccount = oAccount
      Exit For
   End If
Next


With outlookmail
    .To = Sheet1.Cells(2, 1).Value
    .CC = ""
    .Subject = Sheet1.Cells(2, 2).Value
    .Attachments.Add ActiveWorkbook.path & "/attachment1.png"

    .HTMLBody = "hello world!"
    
     'Set .SendUsingAccount = .Session.Accounts.Item(2) 
   
    .Display
   
End With

End Sub

 

[trimShady]

Thank you for the response. I don't know how to create/use VBA in Outlook so this is not gonna help me.

I use this method successfully. I don't know if it's the best, but it works. I do this in Outlook, have not tested in Excel.

I'll keep this in mind going forward.

By the way, it's not a good practice to use "outlook" as a variable name.

Thanks again.

 

[6StringJazzer]

Thank you for the response. I don't know how to create/use VBA in Outlook so this is not gonna help me.

Of course it's gonna help you. I just extended your code using the same technique I use in Outlook. If your original code works in Excel, then my modifications will work in Excel.

 

[trimShady]

I ran your code in Excel and got...
Run-time error '91':
Object variable or With block variable not set

When I click debug, it highlights

.To = Sheet1.Cells(2, 1).Value

When I comment out your code, it runs fine. I even hardcoded the email like so...

.To = "test@test.com"

But getting the same error with your modification.

 

[trimShady]

I got it working. I neglected to replace the "name_of_account" from your modification with the actual 2nd account email. Once I did that, your code worked. Thank you so much.

 

[6StringJazzer]

Glad it worked! I should have emphasized the tailoring needed more prominently.