VBA Send a formatted mail without Outlook

[Forsberg]

Hello,

I know there is a way to send mails via Outlook - once the key is pressed an Outlook window appears and u send a mail manually. I need to use oSMTP to send bulk mails to various recipients.
I have an issue with a template - I tried a template in MS Word, OFT or HTML. This template is a body mail then some parts are replaced by placeholders like<<TEXT>>.

Unfortunately it seems whatever template I use it is always being sent as plain raw text.

1. I used a template.txt, mail was sent correctly - but was just ugly as it has no any format.
2. I used HTML template - mail was sent but Outlook rendered it as raw data (HTML body set) or after messing with it more the mail was completely blank.
3. The same was with OTF (Outlook template) format - either blank or raw data.

Is there a way to send a mail from VBA, without the Outlook itself (so using oSMTP in the background) but which would be formatted by a template I provide? I don't know where I fail.

 

[Micron]

I don't know where you fail either because there is no code to look at. You can write code to create html body but if you use the .Body property, it will be plain text. You'd have to use .HtmlBody. If you still want to send email without Outlook, you can use CDO but you will find this to be more difficult. You will also need to know certain properties of an email server, such as its SMTP address, which may not be possible for servers such as HotMail or Google email services.

 

[Forsberg]

Thanks, I have smtp addressess, ports ect it is for my company, I am building a tool. I will check again your .HtmlBody advice when I get back to my work computer

 

[Forsberg]

Here is the code I am messing with:

Sub Send2()
Dim answer As Integer
answer = MsgBox("Shoul I send?", vbQuestion + vbYesNo + vbDefaultButton2, "Emails")
If answer = vbNo Then
Exit Sub
End If

Application.ScreenUpdating = False
On Error Resume Next

Dim ws As Worksheet
Dim oSMTP As New clsSMTP
Dim strTemplatePath As String
Dim templateContent As String

Set ws = Sheet33
strTemplatePath = "D:\template2.html"

' if no html
If Dir(strTemplatePath) = "" Then
MsgBox "HTML template file not found.", vbExclamation
Exit Sub
End If
' Reading the file
On Error Resume Next
templateContent = ReadFile(strTemplatePath)
On Error GoTo 0

' If no reading
If templateContent = "" Then
MsgBox "Failed to read the content of the HTML.", vbExclamation
Exit Sub
End If

' SMTP settings
oSMTP.SMTPServer = "mysettings"
oSMTP.SMTPServerPort = 25
oSMTP.UseTLS = True
oSMTP.BodyType = olFormatHTML

For i = 10 To ws.Cells(Rows.Count, "F").End(xlUp).Row
' Check if the email is not marked as Microsoft
If Not ws.Cells(i, "U").Value = "MS" Then
' Send email
oSMTP.FromAddress = "myemail@"
oSMTP.ToAddress = ws.Cells(i, "p").Value
oSMTP.Subject = "Subj"
oSMTP.Body = templateContent ' html as a body

oSMTP.EmailSend
End If
Next i

Application.ScreenUpdating = True
MsgBox "Emails have been sent.", , "Success"
End Sub

Function ReadFile(filePath As String) As String
Dim fileContent As String
Dim fileNumber As Integer

fileNumber = FreeFile
Open filePath For Input As #fileNumber
fileContent = Input$(LOF(fileNumber), fileNumber)
Close #fileNumber
ReadFile = fileContent
End Function

 

[Forsberg]

I think I solved it by using HMTLBody, as u suggested earlier. WIll test it deeper!