Outlook Mail Automation Modification

**Liz_Biz** · 04-14-2014, 07:03 AM

Hi All,

I have modified a VBA code from Ron de Bruin Excel Automation.

Its working fine but I want to add something more in this. Please help me to add below steps.

".to" is taking value from column B of excel Sheet 1.
1) So I want ".cc" should take value from column D,
2) ".bcc" should take value from column E and ".
3) ".Subject" should take value from column F.

Below is modified code from http://www.rondebruin.nl/win/s1/outlook/amail6.htm

Please Login or Register  to view this content.

Thanks,
Liz.

**TKCZBW** · 04-14-2014, 08:42 AM

Hi there, how to answer your question depends on whether or not there are equal numbers of entries in columns B,D,E? It will also depend on whether these are the only entries on that worksheet and that you want to send the same e-mail to all recipients.

If so, then I would use:

Please Login or Register  to view this content.

Hope this helps.

**Liz_Biz** · 04-14-2014, 09:02 AM

Hi TKCZBW,

Its not working. Nothing is happening here. No error no results.

Thanks,
Liz

**TKCZBW** · 04-14-2014, 09:12 AM

Hmm. Could you paste your new code in and I will have a look?

**Liz_Biz** · 04-14-2014, 09:41 AM

Please find attachment with code.

**TKCZBW** · 04-14-2014, 09:46 AM

Sorry I can't download sheets whilst at the office, if you post the code like you did first time I'll do my best to fix.

**Liz_Biz** · 04-14-2014, 09:58 AM

Please Login or Register  to view this content.

**TKCZBW** · 04-14-2014, 10:08 AM

I see, the problem is that you have declared your OutApp and Outmail as objects, but then just left it there. You need to define what kind of objects they are:

Please Login or Register  to view this content.

Let me know if it works now!

**Liz_Biz** · 04-14-2014, 10:25 AM

oh. no. Now attachment code is not working.

Please Login or Register  to view this content.

Thanks,
Liz.

**TKCZBW** · 04-14-2014, 11:32 AM

You haven't defined your range (rng).

Also, you appear to be using a 'dir', a file reference and (presumably although I can't see it) a folder reference without any reference to the File System Object. The result of this is that when you append 'FileCell.Value', FileCell has no value.

Either of the above should fix the problem, although for a better understanding of using files/folders I'd go for the second one.

I'd recommend going back to Ron de Bruin's website or the MSDN page on FSOs. His code is very good, but can't be just copy and pasted without making sure all variables are correctly populated and all the objects are properly set.

I'd be obliged if you could accept my answer as that has dealt with the To/CC/BCC/Subject of the e-mail itself, per the initial question.

Best,
TKCZBW

**Liz_Biz** · 04-15-2014, 08:24 AM

Thanks TKCZBW!! I have reached almost at result. Below code is working but with some issues. Please look into this.

1) Code is working only for 2nd row.
2) CC is taking all values in that column except single cell value.
3) Some times getting error 91, "object variable or with variable not set" for code "Set OutMail = OutApp.CreateItem(0)"

Sheet's structure -
Names of the people E-mail addresses Filenames CC BCC Subject

Please Login or Register  to view this content.

Thanks,
Liz

**TKCZBW** · 04-15-2014, 08:42 AM

Liz,

I'm not 100% sure what you want the code to do, some of it appears to be redundant (line 21 for instance, what is it meant to be doing for each cell in column B?).

You are setting the range of the filepath range to encompass all columns between C and Z, which will catch not only file paths but also CCs,BCCs and Subjects. This should be .Range(Cells(2,3),Cells(lastrow,3)).

Declare lastrow as soon as you open the sheet, this means you can use it immediately.

You've changed my suggested code about the '.to' section of the with Outmail section, this won't work as it is only pointing to one cell (as opposed to my method which concatenates all relevant cells in column B.

I'd recommend going over Ron de Bruin's webpage again, step by step, as it is much better than I in explaining e-mail automation. Fundamentally, it boils down to this:

For all parts of the e-mail that have more than one component, saving attachments, (i.e. to,CC, BCC) you can concatenate these into a string, delimited by a semi-colon, then just use that string as the value within the 'With Outmail' section.

As I've answered your initial question, I'd be grateful if you could mark the question as 'solved' and add reputation if any of my advice has helped.

Best,

TKCZBW

**Liz_Biz** · 04-15-2014, 08:45 AM

ok. Done!!

**TKCZBW** · 04-15-2014, 09:08 AM

Thanks.

Hope I was of some help!

Outlook Mail Automation Modification

LinkBack

Thread Tools

Rate This Thread

Display

Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Re: Outlook Mail Automation Modification

Thread Information

Users Browsing this Thread

Similar Threads

Outlook 2007, Referencing mail Item created from outlook template

Send e-mail with image on body (.html) on e-mail manager <> outlook

code to attach the draft mail in new compose mail as attachment in outlook 2010

Outlook Automation Error Problem - Can't locate Outlook Module

Excel Template modification using automation

Bookmarks

Bookmarks

Posting Permissions