Generate Email based off of Form or Query Results

[kduschel]

I have a database to track contracts. I would like to have a command button that will generate our initial email to the customer. Below is the code that I have so far. This coding works; however, I would like to be able to convert this to HTML so I can add some formatting for a better work product. I would love some guidance. Please note that I am 100% self taught so keep the terminology to a level that is more than beginner but not quite an intermediate user! Also, I'm open for any advice to clean up the coding. Thanks!

Sub SendEmail()
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
On Error Resume Next
Err.Clear
Set oOutlook = GetObject(, "Outlook.application")
If Err.Number <> 0 Then
Set oOutlook = New Outlook.Application
End If
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
.To = Me!Email
.CC = "OurEmailAccount"
.Subject = Me!Project & " - " & Me!Type & " Contract No: " & Me!Contract_No & " CO No: " & Me!CO_No
.Body = "At the request of the " & Me!Project & " team, please find attached the following agreement for your review and execution: " & _
vbCrLf & Me!Type & " - Contract No: " & Me!Contract_No & " CO No: " & Me!CO_No & _
vbCrLf & vbCrLf & "In addition, we will require a copy of your Certificate of Insurance and endorsements. For your convenience, we have" _
& " attached a summary of those requirements that can be forwarded to your insurance agent. Please note we need copies of the actual" _


& " endorsements as well as the Certificate of Insurance. " & vbCrLf & vbCrLf & Me!Comments & vbCrLf & vbCrLf _
& " Once executed, please return via email to "OurEmailAccount." & vbCrLf & vbCrLf & "Please let us know if you have any questions." _
& vbCrLf & vbCrLf & "Thank you,"
.Display
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
End Sub

[pbaldy]

.Body

would be

.BodyHTML

or might be

.HTMLBody

I forget which offhand.

[kduschel]

When I do .HTMLBody it does put it in HTML; however it runs everything together. I tried putting some HTML coding in to get my hard returns, etc back unfortunately I get a "FALSE" result when I try to run the email. I'm obviously doing something wrong!

[trevor40]

i use this to create a module in excel for a macro button on the sheet to send an email with html formatting, the dquote is for when it is input into the excel module and the code is converted to text. adjust as required for your situation.

        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Set VBProj = Excel_Workbook.VBProject
        Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
        Dim CodeMod As VBIDE.CodeModule
        Dim CodeMod1 As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """"
        VBComp.Name = "preview"
        Set VBComp = VBProj.VBComponents("preview")
        Set CodeMod = VBComp.CodeModule
        With CodeMod
                LineNum = .CountOfLines + 1
                .InsertLines LineNum, "Sub print_preview"
                LineNum = LineNum + 1
                .InsertLines LineNum, "ActiveWorkbook.Worksheets.PrintPreview"
                LineNum = LineNum + 1
                .InsertLines LineNum, "End Sub"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Sub Send_Mail"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Dim OutlookApp As Object"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Dim OutlookMail As Object"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Set OutlookApp = CreateObject(" & DQUOTE & "Outlook.Application" & DQUOTE & ")"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Set OutlookMail = OutlookApp.CreateItem(0)"
                LineNum = LineNum + 1
                .InsertLines LineNum, "t = ThisWorkbook.FullName"
                LineNum = LineNum + 1
                .InsertLines LineNum, "With OutlookMail"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Application.DisplayAlerts = False"
                LineNum = LineNum + 1
                .InsertLines LineNum, "ActiveWorkbook.Save"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Application.DisplayAlerts = True"
                LineNum = LineNum + 1
                .InsertLines LineNum, ".Attachments.Add(t)"
                LineNum = LineNum + 1
                .InsertLines LineNum, ".Subject = " & DQUOTE & "Employee TimeSheets Workbook" & DQUOTE
                LineNum = LineNum + 1
               .InsertLines LineNum, ".htmlbody = " & DQUOTE & "<br>" & "Hello," & "<br>" & "<br>" & "The attached Workbook contains " & xxx & " Employee " & _
                                      "timesheet" & xx & " for 07-02-15 to 22-02-15" & _
                                      "<br>" & "<br>" & "<br>" & "<br>" & "Regards," & _
                                      "<br>" & "<br>" & "<br>" & "<img src='" & "c:\aaa\logo.png'" & "height=60 width=210" & ">" & _
                                      "<br>" & "<br>" & "<u>" & "Disclaimer." & "</u>" & "<br>" & _
                                      "The information in this e-mail message and any attached file is strictly confidential."
               LineNum = LineNum + 1
                .InsertLines LineNum, ".Display"
                LineNum = LineNum + 1
                .InsertLines LineNum, "End With"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Set OutlookMail = Nothing"
                LineNum = LineNum + 1
                .InsertLines LineNum, "Set OutlookApp = Nothing"
                LineNum = LineNum + 1
                .InsertLines LineNum, "End Sub"
        End With

[pbaldy]

When I do .HTMLBody it does put it in HTML; however it runs everything together. I tried putting some HTML coding in to get my hard returns, etc back unfortunately I get a "FALSE" result when I try to run the email. I'm obviously doing something wrong!

Instead of vbCrLf outside the quotes, you use <br> inside the quotes.

[trevor40]

here's another example for you... it's for an automated email notification system for in/out logistics movements

[CODE] myItem.HTMLBody = "<b>" & "<font size=+2>" & "Outgoing Consignment Notification C/N # " & "</font>" & "<font color= FF0000 >" & "<font size=+3>" & Me.Consignment_Note_Number & "</font>" & "</font>" & "</b>" & "<br>" & "<br>" & _
"Delivery To - " & "<b>" & "<font size=+2>" & Me.Consignment_For & "</font>" & "</b>" & "<br>" & "Via - " & "<b>" & "<font size=+2>" & Me.Courier & "</font>" & "</b>" & _
"&nbsp" & " on " & "&nbsp" & "<b>" & "<font size=+2>" & Format(Me.Date_Dispatched, "dd mmm yyyy") & "</font>" & "</b>" & "<br>" & "<hr width= 500 align= left>" & _
"Consignment Type " & "<b>" & "<font size=+2>" & Forms![consignment note tracking Outgoing].Consignment_Type & "</font>" & "</b>" & _
" Description " & "<b>" & "<font size=+2>" & Me.Description_of_Goods & "</font>" & "</b>" & "<br>" & _
"Number of items Dispatched " & "<font size=+2>" & "<b>" & Me.Number_of_items_listed & "</b>" & "</font>" & _
" Total Weight " & "<b>" & "<font size=+2>" & Me.Weight & "</font>" & "</b>" & " Kg." & "<br>" & "<br>" & _
"Attention to - " & "<b>" & "<font size=+2>" & Me.Attention_to & "</font>" & "</b>" & "<br>" & _
"P/O or Invoice # Reference - " & "<b>" & "<font size=+2>" & Me.po_or_invoive_reference & "</font>" & "</b>" & "<br>" & "<br>" & _
"Notes - " & "<b>" & "<font size=+2>" & Me.Notes & "</font>" & "</b>" & "<br>" & "<br>" & _
"Thank you." & "<br>" & "Logistics department. " & "<hr width= 500 align= left>" & _
"<img src='" & email_logo & "'>" & "<br>" & "<br>" & _
"<font color= " & logo_color & ">" & "Any views expressed in this Communication are those of the individual sender and do not necessarily reflect the views of " & _
Me.Delivery_From & ". This e-mail is confidential and the copyright of " & Me.Delivery_From & _
". If you are not the intended recipient of this communication please delete and destroy it immediately. " & _
"So far as is permitted by law " & Me.Delivery_From & " makes no guarantee regarding the integrity of this communication." & "</font>"[/CODE]