VBA Send a formatted mail without Outlook

Forsberg

New Member
Joined
Apr 17, 2024
Messages
6
Office Version
  1. 2021
Platform
  1. Windows
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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 :)
 
Upvote 0
Here is the code I am messing with:

VBA Code:
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
 
Upvote 0
I think I solved it by using HMTLBody, as u suggested earlier. WIll test it deeper!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top