PDA

View Full Version : Solved: sending emails using vba



photon_ed
05-19-2006, 05:39 AM
Hello,

I will need to run a vba script from excel, which will active outlook to send an email to the relevant email address, a standard text message plus an email attachment. I would be grateful if someone can give me an example and point me to the right direction.
Thank you

yours,
Ed

Killian
05-19-2006, 07:40 AM
Hi Ed,

The basics are quite straight-forward - the problem you will run into is that using the "Send" method will trigger the Outlook Object Model Guard designed to stop Outlook being hijacked but a real PITA when it comes to automation. (There are a few references to this subject in the forum)
Depending on what you're attempting to achieve, this might not be a big deal, so here's the code:Sub SendMailMessage()

Dim objOLapp As Object
Dim objMailItem As Object

Set objOLapp = CreateObject("Outlook.Application")
Set objMailItem = objOLapp.CreateItem(0) '0=mailitem

With objMailItem
.To = ""
.Subject = "subject text"
.Body = "mail body text"
.Attachments.Add "C:\TEMP\testattachment.txt"
.Display 'show the mail
.Send 'send the mail = OMG!
End With

End Sub

photon_ed
05-19-2006, 08:42 AM
Your code works perfectly. Now I just have to read up about formating of the ".body=..." for my email.
Thank you very much!

yorus,
Ed

Killian
05-19-2006, 08:47 AM
Jolly good!

Also have a look at the Bodyformat and HTMLBody properties, which may be relevant...

photon_ed
05-20-2006, 07:37 AM
will do, many thanks :)

yours,
Ed

photon_ed
05-21-2006, 08:00 AM
Hello,

Can anyone tell me how to write a short email(like this one) under the ".body =..." , I am having difficulty working this out. Many thanks.

yours,
Ed

Norie
05-21-2006, 08:11 AM
Ed

Using Killian's example code.


Sub SendMailMessage()
Dim objOLapp As Object
Dim objMailItem As Object
Dim strBody As String

Set objOLapp = CreateObject("Outlook.Application")
Set objMailItem = objOLapp.CreateItem(0) '0=mailitem

strBody = strBody & "Hello," & vbCrLf & vbCrLf
strBody = strBody & "Can anyone tell me how to write a short email(like this one) under the "".body =..."" , I am having difficulty working this out. Many thanks." & vbCrLf & vbCrLf
strBody = strBody & "yours," & vbCrLf & vbCrLf
strBody = strBody & "Ed" & vbCrLf & vbCrLf
With objMailItem
.To = ""
.Subject = "subject text"
.Body = strBody
.Attachments.Add "C:\TEMP\testattachment.txt"
.Display 'show the mail
.Send 'send the mail = OMG!
End With

End Sub

photon_ed
05-21-2006, 08:33 AM
Thank you Norie, I had a go at it however, I seems to be having problems with the empty space line within the email. please advise. Thank you

yours,
Ed

Norie
05-21-2006, 08:42 AM
Ed

What did you try and what was the problem?

photon_ed
05-21-2006, 09:09 AM
My fault, the code is working perfectly! I just forgetten to change ".body=strBody".

Many many Tahnks!

yours,
Ed

Killian
05-24-2006, 02:30 AM
I've marked the thread as solved - hope that's OK...

photon_ed
06-02-2006, 03:19 AM
Thanks Killian :)