Emailing results from XL automatically

[vbacoder]

Hi everyone,

Following on from si the geek's post on automating excel (http://www.vbforums.com/showthread.php?t=391665), I would really like to hear from anyone who could give advice on automatically e-mailing data from a spreadsheet prior to saving and closing.

Thank you very much!

vcoder

[RobDog888]

You can use SendObject in the Workbook_BeforeSave event in Excel.

http://www.vbforums.com/showthread.php?t=402068

Thread Moved

[vbacoder]

Thanks for your reply RobDog,

Could this be applied to worksheets within a workbook, because the workbook I am dealing with has a file size of at least 15MB. Alternatively, saving select results to a txt file is fine. Can I adapt the code to e-mail a newly saved txt file? I would be grateful if you could point me in the right direction.

Many thanks,

vcoder

[RobDog888]

No, that only thing you can email with that method is a workbook.

You can write all your desired data to a secondary temp workbook, open and email from within that instead so your not emailing the entire workbook.

[vbacoder]

I see, for your help RodDog.

Best regards,

vcoder

[vbacoder]

Hi Rob,

Coming back to this, would you happen to know of code to e-mail a text file? It's just that I prefer to write my results in this way if possible.

Thanks,

vcoder

[westconn1]

to send an email from vb /vba, there are several methods, the simplest is
shellexecute "mailto" , but this requires the user to press send
you can automate outlook to send email

otherwise you need to look into cdont,
or vbsendmail, which you can reference the dll to use the sendmail class, but you would need to get the winsock ocx

[vbacoder]

Hi Westconn,

Thanks for your reply. Would you happen to know of any tutorials on the methods you mentioned?

Thanks,

vcoder

[westconn1]

robdog has tutorial for sending with outlook, see top post this forum

search in the vb6 forum for vbsendmail, or google it and download the vbsendmail class and sample vb programs with the instructions
if you don't have vb6 you will not be able to run the sample programs, but the code is easy enough to convert to vba, once you add a reference to the dll, make sure to register the dll