Link to home
Start Free TrialLog in
Avatar of Optima Systems
Optima SystemsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel for emailing

Trying to setup a macro in Excel so that when a cell is populated with someone's email, the macro will automatically send an email to the address in the cell. Does anyone know if this is possible and if so how to create the macro?

Avatar of M A
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Optima Systems


Hi Mas,

Do you know if there is a method to creating a macro without using Visual Basic?

Kind Regards.
You can create a macro and add this code to the macro. 
I am getting a compile error message for the following "User-defined type not defined" which is highlighting the section containing "Dim outApp As Outlook.Application"?

Any suggestions?
For the error
- Stop the macro if it is running
- Go to  Tools > References   in the VBA window
- Make sure that Microsoft Outlook xx.x Library is selected.
That issue is now fixed, but I have another one appearing:
'Run-time error '9': Subscript out of range'
-->'Run-time error '9': Subscript out of range'
Please try to debug and see where is the error.
Try to change according to your settings
When I run the debug, it highlights the following section "ThisWorkbook.Sheets("marketing").Activate"

Not sure what would need changing.

Rename your sheet to Marketing  or change marketing to whatever is your sheet name is.
User generated image
Maybe a different approach....

Have you looked at Mail Merge options in MS Word? One option there is that you can generate emails to a specified distribution list.

You setup your email as a document in Word and then link it to a data file (Excel sheet) for running the merge. No VBA or macros required.