E-mail macro

[slipperyjim]

Hi,
I have a simple e-mail macro that works, however, someone that understands VBA would need to enter the email addresses if they are to change, which it is likely they will.

is it possible to set up this macro so that when clicked it will email out to a list of peole in a cell range?

the cell range: Sheet "Data", Range U17:U39

in these cells are the email addresses typed like this:
firstname.lastname @ business.com.au


not all of the cells in the range will be populated.

if possible could it be set up to just type in the front of the email address as the back is always the same e.g

firstname.lastname (macro code will add the @business.com.au bit)

thanks for any help offered.

 

[Michael M]

Jim
My code runs OK until it gets to

.Display
.send

The "Send" line gives the Automation error

 

[Michael M]

Jim
Change this line:

nameList = nameList & "" & Sheets("Data").Range("Z" & i).Value

to this

nameList = nameList & ";" & Sheets("Data").Range("Z" & i).Value

Mine works fine

 

[slipperyjim]

yeah hopefully someone can.

truth be told this happened before i changed it as per your suggestion

 

[Michael M]

Did you try my line change in #post22

 

[slipperyjim]

geez all because of the little ";" i removed. that will teach me wont it.


thank you everyone very much for your help.

 

[Michael M]

Great news
What about sending the "attachment", eg, one of the sheets in your workbook.
or aren't you sending anything but the Email

 

[slipperyjim]

no mate, no attachments. Purely only to inform those in the contact list of an incident that has happened. it was designed to get notice to the "big bosses' quickly and for them to decide if it should be investigated or not.

 

[Michael M]

No worries
But if you need to send a file, it's simply an extra line in the code.
Strange as it seems, I've been writing macros to send a report to a heap of other people myself.
But when I started with your thread, I realised I could use it to do my mailing as well.
So by helping each other out, I can now send the active workbook to my client list.

Looks like we both win.

 

[diddi]

slipperyjim, is there any way to get this to work for emailing a UserFrom? i have been trying for ages but getting nowhere. sheets are ok but the UserForm is a different beast.

 

[slipperyjim]

i am glad it worked for you mate