Macro to generate an email with cell contents in it...

[willm]

Hey helpful folks !

I have a need to use excel to create roughly 200 emails, with each one containing the contents (in plain text) of unique cells in excel.

See the attachments. The wb shows a few rows of an example dataset.
In column I need to go the macro initiating buttons.

The macro should create a new email to the address in column H.

Each row is unique and therefore in the corresponding text file attached, the cell contents will need to be that of the corresponding row used. So the initiating macro in cell I4 for example, must use information from that row.

I found this:

http://www.rondebruin.nl/win/s1/outlook/amail4.htm

However it was talking about inserting the cells as a worksheet and not as just the contents as plain text. Trouble is, the recipients of the emails do not necessarily have excel.

That's about it really.

[humdingaling]

try using this section instead
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

most is pretty straight forward except for your signature bit

[humdingaling]

see attached file which has been modified slightly

Please Login or Register  to view this content.

modified it to your worksheet
basis of the email body is a worksheet as instead of writing new email from scratch every time

it takes two sections
rng and rng2
top bit then signature then bottom bit

Please Login or Register  to view this content.

[willm]

Wow that's fantastic ! I will give it a try shortly.

Muchas Gracias ;-)

[willm]

There is some sort of issue when opening the spreadsheet you created.

I am opening it with Microsoft office professional plus 2010 on a virtualised windows 7 installation.

I have set all the trust centre settings to allow via / macros / activeX stuff.

When I try to open the file, it errors out saying it cannot open it without removing something first. Then it appears to remove all the code.

See attached screenshots. Thinking it could be a version problem? The finished file will actually be run on office 2007 I think.

Thanks in advance.

[humdingaling]

Please Login or Register  to view this content.

take this section out and try again

[willm]

I can't open it to take the section out :-(

[humdingaling]

the code should work in either 2007 or 2010..in fact 2000 to 2016

the fact you cant even open it probably means something else is not working

you're not on a mac are you?

[willm]

Will try the edited one -thanks.

[willm]

Hmm that's weird, it still won't open, same problem.

[humdingaling]

here is just the file without the code
add the code in yourself thru VBE environment

[willm]

here is just the file without the code
add the code in yourself thru VBE environment

Hi, okay it works okay on a physical windows machine.

One last request !

Say I wanted to build a button at the end of each row that did what it currently does, but just for that row it sent the mail?
And also, the macro as it is spawns all the emails okay, however it does not send them, would be good if the button just sent the email, since there is no reason for me to sanity check it.

Thanks so much for your help with this already, your assistance is invaluable ! ;-)

[humdingaling]

Say I wanted to build a button at the end of each row that did what it currently does, but just for that row it sent the mail?

you can do that...but how many buttons are you talking about?
just feel it will become slow over time when you have 100's of buttons

you can get the row of the button via this code application.caller

below is example.....insert a button and add this to the code
press the button and it should tell you the row it is is

Please Login or Register  to view this content.

as mentioned in code
it takes the topleft point of the button as anchor to return the row

the macro as it is spawns all the emails okay, however it does not send them

change .display to be .send

Please Login or Register  to view this content.

i always use display instead of send while debugging/testing
until you are completely content of the output then use .send

[humdingaling]

heres a link on how to add the button and add the code at the same time

http://stackoverflow.com/questions/4...heet-cell-data

[willm]

Thanks humdingaling. I'd buy you a pint if you weren't so far away.

[willm]

ONE last thing - when the email gets crafted, specifically this:

Subject = "This is the Subject line" ' replace with whatever you want as subject line

What if i wanted to insert "Tape changes for X country", where X is the text content of a cell in the worksheet?

[willm]

ONE last thing - when the email gets crafted, specifically this:

Subject = "This is the Subject line" ' replace with whatever you want as subject line

What if i wanted to insert "Tape changes for X country", where X is the text content of a cell in the worksheet?

ignore that i figured it out :-)

[humdingaling]

glad everything worked out in the end


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.