Send excel file as attachment using gmail in macro?

After digging and tweaking, I'm able to automate sending of email... but unfortunately the file is not attached... Using gmail coding. I know there's no call to attach the file... but not sure what to do at this point.

Any help or direction is greatly appreciated. Thanks!

Please Login or Register  to view this content.

Thanks Leith (looking back through my posts, appears I've said that before...)

I won't know file path, as this is meant to allow different users to use, with variable paths. Is it not possible to send the current file as attachment with CDO? Seems I came across that somewhere, but on earlier versions... hoping that's been fixed?

Thanks

OK... marking this as solved. Took your lead and played around with making the filename a variable. It works... see final code below. Now working on another tweak to this, but in a different thread. Thanks again!

Please Login or Register  to view this content.

Dear fourmurphys,

I've been looking for a similar solution for quite some time now. I'm unsure how this works. Can you please advise me further.

I would only like to get the excel files attached. This will give me the flexibility to check before hitting the send button.

Hope to hear from you!

Best,
Spi

spiwere, the code above is complete, you'd just change some of the variables (sheet name, etc). This uses CDO to send using gmail, since my company switched and we're off Outlook. Prefer outlook, but this is a decent fix...

Okay, got it. Thanks for your reply!

Just one quick help, could you please advise on the changes that need to be made. I'm pretty new to the VBA world, and so need a little more assistance.

Hope this will be not much of a problem!

Best,
Spi

Sure! Please note that I'm not much further along than you... so apologies if this still doesn't help...

Replace all instances of "Copy Instructions" with your particular sheet name. For example, the default is "Sheet 1".
Note, the ChDir line for "C:\Users\Public" only works on Windows 7 (not sure about Windows 8). I'm currently seeking help with that myself.

UserName = InputBox("Enter gmail username (your JRN email address):")
You will want to remove the "(your JRN email address)" line.

objEmail.To = Range("BV1")
I have a cell with the email address of the recipient. If you want this to be an input box, simply replace with a similar InputBox command as with UserName.


objEmail.Subject = "Instructions For- " & Range("B9") & Format(Date, " dd/mmm/yy")
I control the subject line so the recipient knows how to handle. The range indicated is the client name, so you may or may not wish to use. Again, you can use an InputBox here as well if you want to allow that modification.


objEmail.TextBody = InputBox("Advertiser:")
You could replace "Advertiser:" with "Message Text"

objEmail.AddAttachment "C:\Users\Public\" & namedFile & ".xls"
Again, assumes Windows 7 environment.

Thanks a bunch! I'll give it a shot first thing in the morning tomorrow. Hopefuly, it would work as expected. Please advise me further if you happen to get some more modifications.

Its been a while that I've been looking for such a solution!

Thanks again:-)

will do. Good luck!

So here's what I did...

UserName = InputBox("Enter gmail username spiwere@gmail.com:")

objEmail.To = Range("BV1") changed to - UserName = InputBox("Enter gmail username:") - I'm sure this is incorrect!

objEmail.Subject = "Instructions For- " & Range("A4") & Format(Date, " dd/mmm/yy") - How to use an InputBox here? Please suggest!

objEmail.TextBody = InputBox("Hi There, This is a test message for auto emailing:")

objEmail.AddAttachment "C:\Users\Public\" & namedFile & ".xlsx"

I'm using Windows 7 environment. Guess I'm still doing some thing incorrectly or missing another trick

I'm assuming this can loop through and excel and draft n-number of emails using the address from the xlsx. Right?

Thanks again!

Based on what your most recent post, see below for possible fix. Also, if the emails will always be coming from you, you don't need an inputbox for username and password, but could choose to go ahead and hard code that. Assuming that in the fix below. Make sure you replace 'sheet 1' with the exact name of your workbook tab. Also, as long as you use xlsx in the naming and the attachment lines, you'll be fine. If you use below (uses xls), it should still work.

If this doesn't work, please provide error message details. Also... assume you can set to loop, but not sure the best way.

Also... if the list of recipients is unchanging, you could also hard code. Using inputbox allows that to be variable. Good luck!

Please Login or Register  to view this content.

Fantastic! I'll try this and comeback by tomorrow morning.

Thanks!

Dear fourmurphys,

Thanks a ton!

I will test it out and come back shortly. The only thing I'm worried about is how to get a loop done. I need to send well over 200 attachements every alternate day.

Thanks again

Dear fourmurphys,

Check this link on my post.

http://www.excelforum.com/excel-prog...using-vba.html

Maybe it is useful for you aswell.

Thanks,
Spi

Hello there,
How would I go about setting a specific range to be copied to a tempfile and then attached to the email?

Please Login or Register  to view this content.

Tried changing the above, with no success!

Thanks a lot for what this code does already.