Automate sending Outlook emails from Excel VBA

[Roland Hoelscher]

I want to develop an Excel VBA which sends emails using MS Outlook. I basically have a prototype that works (using early binding, I added the reference to MS Outlook, using GetObject, etc.)
When I run the VBA I get a warning message from Outlook which says "A program is trying to send an email message on your behalf . . ." Is there any way to eliminate that warning without changing the Outlook Trust Center settings? In my corporate environment I don't have permissions to change the corresponding Trust Center setting. If my Excel VBA solution needs to send hundreds of emails each time I run the VBA, then clicking on "Allow" in the Outlook warning message is not a feasible solution. Any way I can eliminate or bypass that Outlook warning?

 

[6StringJazzer]

Can you describe what your Excel VBA does? I think one solution would be to port the macro to Outlook. Then Outlook can read the data from an Excel file and Outlook will be doing the sending, which should avoid the warning. This should be fairly straightforward if you are using early binding.

 

[Roland Hoelscher]

Thank you Jeff for looking at this. Attached are the code I created for sending two emails and the warning I get from Outlook for each email. Its just a simple test of driving Outlook from Excel. I have strong Excel VBA skills, but Outlook integration is new to me.

 

[6StringJazzer]

Can you please go to the VBA window, Copy the code then Paste it directly into a post? Then select the code and click the VBA button to preserve formatting. Otherwise I would have to eyeball it and type it all in, which I'm not going to do. Thanks,

You have hardcode what cells have recipient and body. How is this going to work in the real thing? What is the file name of your Excel file? What worksheet contains the data?