Create E-mails in a G-Mail account with attachments using VBA

[spiwere]

Hi Experts,

I’m requesting for some help to be able to create G-emails using Excel (VBA). My request is primarily to be able to attach excel files.

I've attached a sample of my template that will have all the necessary information that I need to relay.

I’ll be happy to provide all / more information. Please advise.

Best regards,
Spi

[OllieB]

spi,

most of your requirements should be covered in the attached workbook. GMail is blocked at work (the smtp ports), so I cannot test. Like I mentioned before "Save as Draft" is not supported by cdo.

[spiwere]

Thank you Sir!

I changed the [email protected] to my address. Populated some of the fields "To" and "CC", Subject body etc...

I also chaged the port number (valid smtp port numbers are 25, 465, 587), but on running I faced the following error:

Please Login or Register  to view this content.

Please advise.

[OllieB]

I should have tested a bit more before posting. sorry. A new/revised version is attached.

[spiwere]

No problems at all!

But now on my run I'm facing a new error..

Please Login or Register  to view this content.

Maybe, Its to do with the our server settings. I'll get this checked once my IT folks are back in tomorrow. They are off and I'm only here to get this going.

Thanks!

[OllieB]

This is a known problem with this method. Try the different port numbers

[spiwere]

Its a miracle or no less than that OllieB.

Just a one question that comes up from my preliminary run is about adding attachments. How should that happen? I have about 200 excel files in a folder. Each one is to be sent to a separate set of recipients.

Thanks again!

[OllieB]

spi, to add an attachment just type the full qualified path to the object you would like to attach. So for example an entry of "c:\temp\abc.xls" in column L would attach that spreadsheet to the e-mail being send.

[spiwere]

Yes, I think I had guessed it after I wrote the last post. It is working fine. I'll do a more comprehensive run once I get back to close this thread or seek any help.

Thanks again!

[jph89]

I absolutely love this functionality. It is exactly what I need.

Unfortunately, when I run the macro the email just contains the text of the path but not the attachment. Any ideas?

[jph89]

Nevermind. Got it!

[spiwere]

Hey Jph,

Sorry for the late reply. I think you have been able to resolve it. In any case, for adding an attachment please type the complete path of the desired file you would like to attach. So for example an entry of "c:\temp\abc.xls" in column L would attach that spreadsheet to the e-mail being sent.

Thanks!

[spiwere]

Dear OllieB,

I'm running this and everything seems to be in place till now. The finalization at my end would take sometime as the CDO limitation to generate drafts forces me to incorporate everything on this excel and be more than 100% sure, and believe me with the humungous data I have it isn't going to be that easy. I'm up for a good few days of extra work. But your solution is Awesome!!!

Just one quick thing: How can one add a Send and an Attach button onto the this. Right above the header would do.

Thanks!

[OllieB]

spi, you can try this yourself quite easily. Go to the Developer tab on the ribbon, and click Insert. Select the button image under Form Controls. Draw the button where you want it to be and you will automatically be prompted to select the macro you want to attach to the button. Not sure what you would like to do with the Attach button though

[spiwere]

Thanks OllieB!

All is well. I'm going ahead and closing the thread as "Solved"

Best regards,
Spi

[OllieB]

Spi, please remember to click on the star icon if you are happy with my contribution! Thank you

[Peterconway29]

Hey I've been searching the net for a code that works I toy with it just to get work emails to send through excel. I'm using the code you attached which covers most of my needs. I've cleared all the references to the cell on the to section just to the email I want to send to this works. I've tested the attachment section by adding a pathway to another file just to see If I can send with an attachment and this worked. I have linked the macro to a command button which I would like to use when i'm finished with the workbook. I want it to effectively save the workbook and then send it on using your code

[alansidman]

@peter

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.