Re: Loop through the rows and extract unique email id's to generate email drafts

[spiwere]

Hi All,

I have this fabulous Outlook template that helps me generate emails on Outlook. However, I need some help to enhance the script to do the following things:

Modify the current subject line “Performance Metrics for the month of November – 2013” TO ABC Inc / Performance Metrics for the week of January 17, 2014

1. Company Name is to picked up from Sheet1 cell A3 onwards and appended as displayed in blue above
2. The week of date is the Friday’s date of each week
3. Is there a way to add a signature with images in the script? If yes, can that be done. I would like to add my companies logo as a part of the signature

Please let me know if the request is unclear. I will be happy to provide more information.

Thanks in advance!

Spi

[xlbiznes]

try this updated file of yours , with the changes made.

[spiwere]

Thanks a billion. This works great!!!

However, I need 1 slight modification. Can the logo's be added to the MailTemp sheet itself. I would like it to start 1 row below the signature. There is also a disclaimer of sorts that I'll add at the end of the email i.e. after the logo.

Please help and advise.

Thanks again

[xlbiznes]

Your email is using html format , so even if you have a shape (picture) in your temp sheet , we need to file path to attach the same in the html body.

If you want to add your disclaimer you can add it to this line of code , like this :

Please Login or Register  to view this content.

[spiwere]

I get it thanks a lot. Very much appreciated!!!

May I very humbly ask you for one additional favour. Is it possible to pick the email address by recipient company in case the emails are stored in my data base like in the attached way. I request this because in my testing phase I realized that maintenance of the email list in the current way is prune to errors.

Please see attached and advise. If this can be done. I'm all set.

Thanks,

Spi

[xlbiznes]

from what i understand from your new request is that :

you have a separate file with the list of email ids , you want to collate all the id's for that company and send the email's out.

this has 3 steps,

first ,filter your email db wkb for the specific company ,
second, loop through the visible rows and extract unique email id's
finally , pass those email id's to your mail sending routine.

[spiwere]

Yes, that is exactly the case. The only thing being I'll filter copy and paste the sub-set from the database to this macro "Sheet 1" to get the drafts.

Thanks,

Spi

[spiwere]

Your post title should accurately and concisely describe your problem, not your anticipated solution. [/B]

Dear Sir,

My sincerest apologies. I'll surely amend the title right away, and will also keep this in mind in future.

I've changed the title to: "Loop through the rows and extract unique email id's to generate email drafts"

Please review and let me know if it is still not per rules of this fantastic forum. I'll be more than happy to revise it, across all posts of this thread.

Best,

Spi

[spiwere]

God Day!

Reference post 5 of this thread. May I please request for your assistance please.

Thanks in advance!

Best,

Spi

[xlbiznes]

try this file and attached is also a sample email db file , make sure that the file exists in the same folder as your main xlsm file
or change the code accordingly to refer to the actual file path.

[spiwere]

Thanks xlbiznes. Really appreciate your efforts!

It is giving me an error. That the email db xlsx can not be found Run time error 1004. Perhaps I'm doing something wrong. Please advise.

For testing: I'm using the email db file you attached in post 10

My steps:

1. Pasted both the files in 1 folder
2. Opened Outlook
3. Clicked "Generate Emails from DB"

But I get the above error...


Alternatively, wouldn't it be possible for me to not use 2 separate files? In others words - If I can copy the recipients to whom the email needs to be dispatched into the xlsm sheet itself to generate the drafts...

Please advise...

Regards,

Spi

[xlbiznes]

i am unable to simulate your error. its working fine on my side.


can you try using the relative file path in this line

from:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[spiwere]

I just left for home. Saw your response so stopped by to answer. I'm on my way home so will take a look in a few hours. On the phone now....I'll respond ASAP.

You've been extremely helpful. I'm delighted to be a part of this forum.

Thanks a zillion times!

Please excuse any typos, message composed via mobile phone!

[spiwere]

Changed to:

Please Login or Register  to view this content.

[/QUOTE]

I'm testing it out now. It seems to be working fine with the latest input you provided. I'll test it with my real data once I get back to work in the morning, and will comeback to close this thread.

Thank you very very much!

Best,

Spi

[spiwere]

try this file and attached is also a sample email db file , make sure that the file exists in the same folder as your main xlsm file
or change the code accordingly to refer to the actual file path.

WOW...Simply WOW! This is working absolutely fine. Thanks so much for your assistance. I learned 1 more thing.....

However, there seems to be a slight issue - on pulling the email list from the system. I discovered that the database (My Real Database) throws the recipient list as in the attached way. My bad I should have pulled the report in the very first instance - I'm sorry, but guess we all "Live and Learn".


Could you please take a look and advise onto what changes I should incorporate to make this work. I tried a lot of permutations and combinations, but couldn't find a way out.

Thanks a billion... You've provided me a master piece!

Thanks,

Spi

[xlbiznes]

it looks like this :

Column B : To
Column C to E: CC
Column F : BCC


i hope this is the way in which you need to pull out the info ?

[spiwere]

it looks like this :

Column B : To
Column C to E: CC
Column F : BCC


i hope this is the way in which you need to pull out the info ?

Exactly Sir! Thank you!

[xlbiznes]

check this updated file.

[spiwere]

Thanks this works great. Only 2 points

- I am unable to move the "Get Emails from DB" control button to a different destination in the sheet, whereas I can do that quite easily for the "Send Emails" command button I had created. Not sure why this is happening. Is it protected or so?

- I changed "y= 1" in this row of the code:

However, the emails were only getting generated from B3 cell onwards, and not B2. It is skipping the first email.

I then changed the range in this row:

to

and it worked. Is this correct?

Please advise and we are all set to close this thread after this.

Very very thankful.

[xlbiznes]

Click and then right click the control in the design mode.

Select Format Control and then select the Properties tab, Select the appropriate option.

[spiwere]

Click and then right click the control in the design mode.

Select Format Control and then select the Properties tab, Select the appropriate option.

Great... Works now....

One final thing - The change that I stated above is correct. Or any further changes are needed. It seems to work perfectly though....Just want to be sure though.

In any case, I'm closing the thread as "Solved".

Thanks,

Spi

[xlbiznes]

your code makes sense as you have changed it to suit your actual file. Looks good to me.

See you around

[spiwere]

your code makes sense as you have changed it to suit your actual file. Looks good to me.

See you around

Thanks xlbiznes. You truly rock!

I was running it against my database and noticed a weird thing - One of my companies has an alpha numeric name example: 3ABC Corp in this case the results get populated as follows:

To Field: To;[email protected]
CC Field: CC;[email protected];[email protected];[email protected]
BCC Filed: BCC;[email protected]; [email protected]

I'm not sure why TO, CC and BCC are getting populated. Could you please advise as to what may be causing this issue...

Thanks again...

[xlbiznes]

can you post a sample email db for me to see what the issue is ?

from your statement i am assuming that the email id's are blank in your email db but it gets populated with irrelevant data in your xlsm file.

[spiwere]

can you post a sample email db for me to see what the issue is ?

Sure...Here is the email database sample.

[xlbiznes]

i tried to simulate the problem :

this is what i get :

3ABC Corp [email protected] [email protected];[email protected];[email protected] [email protected]; [email protected]


and this is the data in your file
Company Name To CC CC CC BCC
3ABC Corp [email protected] [email protected] [email protected] [email protected] [email protected]; [email protected]


it seems to match with email db , what is the issue ?

[spiwere]

Strange! I was getting:

To Field: To;[email protected]
CC Field: CC;[email protected];[email protected];[email protected]
BCC Filed: BCC;[email protected]; [email protected]

Let me rerun it. I'll get back ASAP.

Sorry, but this seems to be quite strange!

[xlbiznes]

change this line of code

from :

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[spiwere]

On changing to

Please Login or Register  to view this content.

[/QUOTE]

It only gets emails for the first 2 entries from the database. I only get error on the first line. The pull looks like this:

3ABC Corp To;[email protected] CC;[email protected];[email protected];[email protected] BCC;[email protected]; [email protected]

However, it is okay for the rest, when I use x = 1.

Thanks.