Generate an e-mail from worksheet

[mpower87]

Hi guys, im trying to generate multiple e-mails from a sheet with a check for duplicates.

Example:

Column A contains data "[DATA1]", Column B contains data "[DATA2]", Column C "[DATA3]" contains data , all data is to be included in the body of the message.

Column D contains an e-mail address "[EMAIL]", Column E contains a name "[NAME]".

There are multiple rows of data & corresponding e-mail addresses & names.

What I would like is a macro that would check for duplicates in Column D and if found combine the data in each row to a string that would then go into the e-mail body.

I've attached an example. The final e-mail would like something like this (example e-mail is to an customer who has multiple rows in the sheet - these could be ordered randomly in the sheet):

To: [EMAIL]
Cc: (a value stored as string - this value is static)

Hi [NAME],

The current status of your orders is:
[DATA1a] is currently [DATA2a] and it would be ready on [DATA3a] (<- data from a row in the worksheet)
[DATA1b] is currently [DATA2b] and it would be ready on [DATA3b] (<- more data row from another row in worksheet)

If you have any questions please don't hesitate to contact me.

Regards,

Me

If anyone knows what the best way of doing this would be I would really appreciate your input.

[delforum]

I usually use Microsoft Word Mail merge to setup the mailing from an excel list. It has a wizard that would guide you through.

As for combining the multiple entires to Bob in one email: I would think you could use VLookup to fill in the additional blank columns with the extra data. The second row of data you find for Bob, could be added to his last empty cell. Then run the mail merge.

[mpower87]

Not sure how well that will work. My sheet changes constantly and I would like to create e-mails with a click of a button via the macro. I will be utilising Outlook for this. I can easily send regular pre-defined e-mails from my sheet but not customised with Data/names/addresses.

If anyone can assist even with some tutorials etc I will be very thankful.

[jaslake]

Hi mpower87

I've done a bit of this. Your issue is a twist I've not dealt with but I've some ideas

Please Login or Register  to view this content.

If no one gets back to you tonight, I'll look at it tomorrow.

[mpower87]

Hi mpower87

I've done a bit of this. Your issue is a twist I've not dealt with but I've some ideas

Please Login or Register  to view this content.

If no one gets back to you tonight, I'll look at it tomorrow.

Awesome, glad to hear that someone is interested in tackling this one. Let me know if I can help with anything!

[jaslake]

you'll hear

[jaslake]

Hi mpower87

Let's start with the attached and see where you want to go with it.

Let me know of issues.

[mpower87]

Hi mpower87

Let's start with the attached and see where you want to go with it.

Let me know of issues.

Wow thanks jaslake!
okay that's looking great and working really well, now for the real problem :P:

I've attached the sample doc that will need to have this functional in. the main issue is that the columns from which DATA1/2/3 will be selected are not necessarily next to each other. E.g. DATA1 will come from Column "D" (order number), DATA2 will be Column "E" (End User) and DATA3 will be Column "J" (current ETA).

Names and emails are drawn from Columns "Q" and "P' respectively.

Also need to note that names are not unique - email addresses are however. Not sure but from what I could tell in your script was it looking for unique names as opposed to unique e-mails??

Sorry if I made this too complex, I wanted to see if I could transfer the macro to my worksheet (attached) and just customise it for my needs but it seems to be out of my league.

Appreciate any help you can provide.

[jaslake]

Hi mpower87

in your script was it looking for unique names as opposed to unique e-mails

I focused on this 'cause that's what you told me you wanted

In this example Bob would need to receive ONE email containing both rows of data.

Update your file to reflect this

names are not unique - email addresses are however

I don't see examples of this in the attached file. This, of course, makes things a bit more complex. Are there anymore

now for the real problem

issues?

[mpower87]

hi John,

Thanks for your response again. Sorry for misleading with the name thing.
Problems I see at the moment:
1. How to make the script pick out different columns as opposed to just expanding selection from A->C and copy/paste.
2. Notice in the attached doc there are two customer named "Team" however have different e-mail addresses. Also two "Angela"s with different emails as well. These are two different customers and need to have different e-mails sent with corresponding orders.
3. Is there any way to get the customer's name to go in top of the e-mail e.g. "Hi Angela,"

really appreciate your help mate

[jaslake]

Hi mpower87

I'll look at it...any other issues I need to know about?

[mpower87]

Hi mpower87

I'll look at it...any other issues I need to know about?

Thanks John,
Just one last thing, headings need to be: PO | End User | ETA. The the data below the headings would be fine.

Email should look like this when generated:

To: [email]
Hi [name],

The current status of your orders is:


PO | End User | ETA
1204294 | Example EU | 03/02/11
2894988 | Sample User | TBA

Thanks and kind regards,

Max

Thanks heaps

-Max

[jaslake]

Hi mpower87

The Email addresses in Column P appear to be unique. Will this be the case?

[mpower87]

Hi mpower87

The Email addresses in Column P appear to be unique. Will this be the case?

Some e-mails may be the same (there may definitely be duplicates/triplicates), some may be unique. Names will definitely not be unique.

[jaslake]

Hi mpower87

So, if one has the same email address with 3 different names, the same email will be sent 3 times; once to "Hi Sam", once to "Hi Pete" and once to "Hi Mary"?

[mpower87]

Hi mpower87

So, if one has the same email address with 3 different names, the same email will be sent 3 times; once to "Hi Sam", once to "Hi Pete" and once to "Hi Mary"?

technically yes. however this will not happen as names and addresses will always be paired correctly. let me illustrate with an example:

emailaddress1@a.com | Bob
emailaddress2@b.com | Bob
emailaddress1@a.com | Bob
emailaddress3@c.com | Bob

in the above case these are three separate customers from 3 separate companies meaning they should get only information about their orders. Customer named Bob from company a would get 1 email with 2 rows of data. Customers named Bob from company b and c would each get one email with one row of data.

Does that make sense?

[jaslake]

Hi mpower87

I think I understand. I believe your sample data does not reflect this scenario. I could build it from your existing data but I'd be guessing. Please update your attached workbook to reflect the possible scenarios so I have your actual environment to work with.

It's going on midnight here...I'll get back to you tomorrow. If you have the time this evening, post an updated workbook.

[mpower87]

Hi mpower87

I think I understand. I believe your sample data does not reflect this scenario. I could build it from your existing data but I'd be guessing. Please update your attached workbook to reflect the possible scenarios so I have your actual environment to work with.

It's going on midnight here...I'll get back to you tomorrow. If you have the time this evening, post an updated workbook.

Cool, thanks for still remaining interested! I've modified the file a little and added some notes to the right of the name column showing the different cases.

[jaslake]

Hi Max

Got it...will get on it in the AM..Should be doable, maybe...perhaps

[jaslake]

Hi Max

Try the attached. Let me know of issues.

[mpower87]

Hi Max

Try the attached. Let me know of issues.

Hi John,

That works really great, thanks a million so far!

One thing I would like to see as a slight modification:

Would like to add a trigger in column "O" as to whether an email is needed to go out, if value in "O" column of row=Y the row will be used, if value in "O" column of row=N the row will be skipped and not included in an email. Is that possible??

[jaslake]

Hi Max

Yes, this is possible; it'll take me some time to work out the logic

Would like to add a trigger in column "O" as to whether an email is needed to go out, if value in "O" column of row=Y the row will be used, if value in "O" column of row=N the row will be skipped and not included in an email.

Will Column O ALWAYS be populated with either "Y" or "N"?

Any other

slight modification

Won't get to this tonight.

[mpower87]

Hi Max

Yes, this is possible; it'll take me some time to work out the logicWill Column O ALWAYS be populated with either "Y" or "N"?

Any other

Won't get to this tonight.

I think best would be to go with: if column O is empty then exclude row, if value=Y then include in e-mail generation.

I think thats about it. It's a perfect solution for me otherwise

[jaslake]

Hi Max

The attached appears to do this

if column O is empty then exclude row, if value=Y then include in e-mail generation

I've taken a slightly different approach as you'll see.

Let me know of issues.

[jaslake]

Hi Max

If this satiisfies your requirements, please mark your post as "SOLVED".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

[mpower87]

Perfect, thanks very much!!!!