Macro???
Posted by Bill on August 24, 2001 7:39 AM
I am looking for a way that I can have a user of a form click a button that will open an Outlook new message and attach the current form into it, as well as insert a particular "to" e-mail address to the "to" line of the new message window.
I tried tinkering with macros, but the closest I could get was opening the new message window with the right address, but not with the file attached.
Is there a way to do such a thing?
Thanks.
Bill.
Posted by Tom Urtis on August 24, 2001 9:17 AM
Re: Here's one way to do it
Hi Bill:
You can paste this code into your procedure (after customizing it to suit your file name, send to, CC, and body message text, etc).
One tip, keep in mind that the ".Display" line in the With structure will just display the email. If you want to cut to the chase in your code and simply have the email get sent, replace ".Display" with ".Send".
Also with this code, Outlook does not need to be open when the code is run so it should be convenient for you in that respect.
Hope this helps,
Tom Urtis
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "JohnDoe@anywhere.com; JaneSmith@anyplace.com"
.CC = MaryJones@anycompany.com
.Subject = Todays Report"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Bill" & Chr(13) & Chr(13)
.Attachments.Add fName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
Posted by Bill on August 24, 2001 10:06 AM
Re: Here's one way to do it
Um... Okay. I gather this is a macro code that I paste into the virtual basic editor? (If I can remember how I did that the first time!)
I have almost NO experience with macros or handling code.
Could you elaborate a little further please?
Thanks.
Bill.
Posted by Bill on August 24, 2001 11:20 AM
OH! I got it... but what about...
OH! Okay. I got the macro to work with a clickable button. THANK YOU SO MUCH!!
...but while I'm at it, is there a way to have the "regards" automatically select the name of the person who is sending the e-mail? (from:)?
Posted by Tom Urtis on August 24, 2001 1:59 PM
Re: OH! I got it... but what about...
Bill --
If you are referring to the name in the body of the emial, I'd defer to someone else who knows if that is(n't) possible. Otherwise, the sender's name in the "From" line of Outlook will automatically appear as the name assigned to Outlook or logged on to that computer from which you are working if you are on a LAN or WAN in your office, which I presume you are if you are using Outlook for your inter- and intra-office email carrier.
Sorry about just dumping the code on you without the prelims earlier, I thought you had been working in VB code already given the question but it sounds like you worked it out since.
Good luck.
Tom
Posted by Bill on August 27, 2001 8:07 AM
Thanks...
Thanks again.
Regards,
Bill Bill -- If you are referring to the name in the body of the emial, I'd defer to someone else who knows if that is(n't) possible. Otherwise, the sender's name in the "From" line of Outlook will automatically appear as the name assigned to Outlook or logged on to that computer from which you are working if you are on a LAN or WAN in your office, which I presume you are if you are using Outlook for your inter- and intra-office email carrier. Sorry about just dumping the code on you without the prelims earlier, I thought you had been working in VB code already given the question but it sounds like you worked it out since. Good luck. Tom : OH! Okay. I got the macro to work with a clickable button. THANK YOU SO MUCH!! : ...but while I'm at it, is there a way to have the "regards" automatically select the name of the person who is sending the e-mail? (from:)?
: Um... Okay. I gather this is a macro code that I paste into the virtual basic editor? (If I can remember how I did that the first time!)
