Solved: sending emails using vba

[photon_ed]

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]

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:[VBA]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[/VBA]

[photon_ed]

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]

Jolly good!

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

[photon_ed]

will do, many thanks

yours,
Ed

[photon_ed]

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]

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]

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]

Ed

What did you try and what was the problem?

[photon_ed]

My fault, the code is working perfectly! I just forgetten to change ".body=strBody".

Many many Tahnks!

yours,
Ed

[Killian]

I've marked the thread as solved - hope that's OK...

[photon_ed]

Thanks Killian