Automatically sending email from a shared mailbox via an Excel macro

[Jeff Leubecker]

This is the relevant code that will loop through a worksheet X number of times and will send an email to a distinct email address in each row of that worksheet (I replaced the NewEmailItem.To field with just a generic email for posting). Say my email is me@mycompany.com. I want to send the email as coming from AP@mycompany.com. Everything is set in Outlook and on the exchange server to do this. AP@mycompany is just a shared mailbox and me@mycompany (365 Business Standard account) has full delegated rights to send as AP@mycompany. I can manually do so. Me@mycompany is the only account in my outlook.

What I want this to do is loop through and automatically send the email from AP@mycompany.com. This code shows .send as a remark statement because presently I can't get it to work. It will only send from me@mycompany.com. What I have to do to get it to work is display the message (.display True), manually change the From box in the displayed outlook message to AP@mycompany.com and click send. The code will then loop to the next line and I can manually send them all that way, and they all send as coming from AP@mycompany.com.

I would like to not display at all. Can I automate what I have to do manually here? I'm very new to doing anything with outlook in vba. Any help would be greatly appreciated.

Dim EmailApp As Outlook.Application
Dim NewEmailItem As Outlook.MailItem

Set EmailApp = New Outlook.Application
For X = 2 To ActiveSheet.UsedRange.Rows.Count
      Set NewEmailItem = EmailApp.CreateItem(olMailItem)
      <Code that generates email body as string VData>
      With NewEmailItem
            .To = "[EMAIL]abc@xyz.com[/EMAIL]"
            .Subject = "ACH Remittance Advice from My Company"
            .HTMLBody = VData
            '.Send
            .Display True
      End With
      Set NewEmailItem = Nothing
Next

 

[Jeff Leubecker]

This is the relevant code that will loop through a worksheet X number of times and will send an email to a distinct email address in each row of that worksheet (I replaced the NewEmailItem.To field with just a generic email for posting). Say my email is me@mycompany.com. I want to send the email as coming from AP@mycompany.com. Everything is set in Outlook and on the exchange server to do this. AP@mycompany is just a shared mailbox and me@mycompany (365 Business Standard account) has full delegated rights to send as AP@mycompany. I can manually do so. Me@mycompany is the only account in my outlook.

What I want this to do is loop through and automatically send the email from AP@mycompany.com. This code shows .send as a remark statement because presently I can't get it to work. It will only send from me@mycompany.com. What I have to do to get it to work is display the message (.display True), manually change the From box in the displayed outlook message to AP@mycompany.com and click send. The code will then loop to the next line and I can manually send them all that way, and they all send as coming from AP@mycompany.com.

I would like to not display at all. Can I automate what I have to do manually here? I'm very new to doing anything with outlook in vba. Any help would be greatly appreciated.

Dim EmailApp As Outlook.Application
Dim NewEmailItem As Outlook.MailItem

Set EmailApp = New Outlook.Application
For X = 2 To ActiveSheet.UsedRange.Rows.Count
      Set NewEmailItem = EmailApp.CreateItem(olMailItem)
      <Code that generates email body as string VData>
      With NewEmailItem
            .To = "[EMAIL]abc@xyz.com[/EMAIL]"
            .Subject = "ACH Remittance Advice from My Company"
            .HTMLBody = VData
            '.Send
            .Display True
      End With
      Set NewEmailItem = Nothing
Next

Nevermind. I figured it out. Just before .send I put in .sentonbehalfofname="AP@mycompany.com" and it worked. I can remove .display and all emails sequentially generate and send from AP@mycompany.com. Thanks to anyone who looked at it and considered.