Copy range to new workbook, save and email

**romperstomper** · 10-26-2010, 10:10 AM

Would have been simpler if you'd copied and pasted my code into your workbook rather than taking random bits of it...

anyway, you need to alter this line:

        .Range("A2:B" & NR & ",E2:E" & NR & ",G2:G" & NR & ",J2:L" & NR).Copy Destination:=Destwb.Range("Area")

to this:

        .Range("A2:B" & NR & ",E2:E" & NR & ",G2:G" & NR & ",J2:L" & NR).Copy Destination:=Destwb.Worksheets("Sheet1").Range("Area")

**mcinnes01** · 10-26-2010, 10:21 AM

Ok,

I have copy and pasted your code and replaced that line..

I think we are nearly there, the error message is saying

Message 'save as' of object 'workbook_' failed

**romperstomper** · 10-26-2010, 10:21 AM

Yeah, I just noticed that - the SaveAs line should be:

       .SaveAs SaveStr & FileExtStr, FileFormat:=FileFormatNum

**mcinnes01** · 10-26-2010, 10:28 AM

Thats cracked it! Thanks

You don't know how I can get it to put the To address in for the email?

**romperstomper** · 10-26-2010, 10:47 AM

You should already have that in your code, no?

**mcinnes01** · 10-26-2010, 10:55 AM

I though I did but it is just opening the email and filling out the subject and attaching the file

I will say I am using First Class as my email application (its a college so we don't have outlook, sadly)

**romperstomper** · 10-26-2010, 10:57 AM

It works for me, putting "me@myemail.ac.uk" as the address. I am using Outlook, though. Never heard of First Class, but if it's MAPI compliant then it should work.

**mcinnes01** · 10-26-2010, 11:06 AM

yeah it has mapi but its called fcsmapi.dll does that matter or not?

Can it also send the email completely, as in you don't press send?

Cheers,

Andy

**romperstomper** · 10-26-2010, 11:07 AM

I've no idea what fcsmapi is as I assume it's proprietary. For me, your code sends the email rather than displaying it (once you say yes to the Outlook security prompt).