Automatic mail with a dynamic range of data

[Tha_Milo]

Hello everyone,

I'm new to the forum and thought I would jump right in with a VBA question! I've no VBA coding experience, but I have an issue that can be solved using VBA code.

I have an excel file with multipe rows....
Each row has multiple columns, last column contains an e-mail adress

What I want to achieve is the following:

-> I want a mail to be created with all rows containg data for each mail adress <-

Let us have an example....

We have an xlsx with 100 rows, containing Columns A to F, with column F being the column containing the email adress.

Presuming that mail adress for Row 2 to 10 is similar, then there should be a copy of A2 to E10 into the body of the mail

Following range selection should be until the new mail adress changes ( presuming this would be on row 25, then data range should be A11 to E25)

I searched for some help on the net, and allready found out that

-> 'ActiveSheet.Range("A2:F100").Select' will give me a fixed range, being all my data.

-Mailcan be ent using this :

ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "Body")
.Item.To = "[email protected]"
.Item.Subject = "Title")
.Item.Send

But that will be the only thing I know.... I have got no clue how to do this.

If anyone could be of any assistance, Any help or hints will be very much appreciated .

Thanks in advance!!

Ruben

Please find an example in attachments :-)

[estratosphere]

Tha_Milo,

Welcome to the forum!
It may be worth taking a look at Ron DeBruin's website - he covers pretty much everything you need to know about mailing from Excel and using dynamic ranges etc.

http://www.rondebruin.nl/win/section1.htm

Simply select the method of mail you want to use and there are lots of different VBA modules available depending on your needs.
Hope it helps!

[Tha_Milo]

Thx estratosphere,

But i do not see any dynamic range declaration on that page...
Also , he does paste som codes, but if we want to create a nice mail, he just menstionens the lines to add, but never specifies where i have to add them
I think this will be a base for the code to work on : http://www.rondebruin.nl/win/s1/outlook/bmail2.htm OR this one http://www.rondebruin.nl/win/s1/outlook/bmail3.htm

Would it be some nasty issue to create me that code? for I do not even have a clue how to start... (I'm very sorry for being such a VBA Noob )

KR

Ruben

[Tha_Milo]

Is there anyone who could be of any assistance?

[jaslake]

Hi Ruben

Is there a reason you're using MailEnvelope vs this link?

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

[jaslake]

Hi Ruben

The attached has Code from the Link I referenced...see if it does as required.

CTRL + x will fire the Code.

[Tha_Milo]

Jaslake,

Indeed this does the trick....

How can we use this so the Subject field contains last month (aug/2014) and the body contains some text (& perhaps the signature of the user)?

[jaslake]

Hi Ruben

The Code in the attached has been modified to include some verbiage and to insert the Active Users Default Signature and Username.

Be aware, in Outlook, the User will need to have a Default Signature and that New Mail Messages will need to be set to accept this Default Signature.

[Tha_Milo]

Jaslake,

You are a true hero... the term Guru is placed well :-)

Thanks , I owe you!

This can be closed!

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

Please mark your Thread as SOLVED.