Outlook VBA: Forwarding an email received in shared mailbox, setting FROM to a specific user account

Question

I receive invoices from suppliers that I want to quickly forward to a QBO (Quickbooks Online) email address that handles 'receipts/invoices' wherein it reads the attachments and parses the info within - this speeds up data entry.

The problem is that QBO only accepts these emails from specific email addresses (i.e., ones that are registered as accounts in QBO). So assume that QBO only accepts emails from "[email protected]". However I receive the invoices on "[email protected]" which is a shared Office 365 mailbox that "[email protected]" has access to.

So my macro (VBA) should forward the currently selected emails (found within the [email protected] mailbox) using the [email protected] sending address to [email protected].

The problem is that the forwarded email arrives in the recipient's mailbox as having come from [email protected]. When I .Display (instead of .Send), I see that the sending account is set "correctly" yet it still arrives from the wrong account. So I figured that something superficial is happening - I decided that after the window pops up (using .Display), I would change the sending account to something else, then back to the intended sending account - and lo and behold, it works. So there's some setting/headers other than .SentOnBehalfOfName that I need to set to get this working as I don't want any user intervention.

Option Explicit

Public Sub SendToQBO()
  Dim Email As Object
  Dim Sender As String
  Sender = "[email protected]"
  
  For Each Email In ActiveExplorer.Selection
    With Email.Forward
      ' Just send to myself for now until this is figured out
      .To = Sender
      '.To = "[email protected]"
      .Subject = "Sent From Outlook"
      .Body = Email.Body
      
      .SendUsingAccount = Session.Accounts(Sender)
      .SentOnBehalfOfName = Sender
      .Send
      ' Using .Display instead shows the right sending address, but it's ineffective
      ' unless I select another, then select it again before manually sending.
      ' .Display
    End With
  Next
End Sub

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2021-06-27T20:09:46

There is no need to set up two properties simultaneously on the mail item.

The MailItem.SendUsingAccount returns or sets an Account object that represents the account under which the MailItem is to be sent. The SendUsingAccount property can be used to specify the account that should be used to send the MailItem when the Send method is called. Note, corresponding account should be configured in Outlook:

Sub SendUsingAccount()
 Dim oAccount As Outlook.account
 For Each oAccount In Application.Session.Accounts 
 If oAccount.AccountType = olPop3 Then 
 Dim oMail As Outlook.MailItem 
 Set oMail = Application.CreateItem(olMailItem) 
     oMail.Subject = "Sent using POP3 Account" 
     oMail.Recipients.Add ("[email protected]") 
     oMail.Recipients.ResolveAll 
 Set oMail.SendUsingAccount = oAccount 
     oMail.Send 
 End If 
 Next 
End Sub

In case given permissions to the shared account you can use the MailItem.SentOnBehalfOfName property which allows setting a string indicating the display name for the intended sender of the mail message. In that case the account may not be configured in Outlook.

And the last bit which I've noticed in your code:

For Each Email In ActiveExplorer.Selection

Remember, the selection object may contain different kind of Outlook items - appointments, posts, mail items and etc. So, I'd suggest adding a check for the item type before accessing MailItem methods and properties.

Outlook VBA: Forwarding an email received in shared mailbox, setting FROM to a specific user account

1 Answers