email through excel

Nichole09

Board Regular
Joined
Aug 27, 2016
Messages
127
Hello All,


I am struggling with my email macro. I found a great one online that attaches a worksheet as a PDF as I need it to, but cant get the body right. I need certain items in the body to be bold and underlined. After doing some research it seems the only way to accomplish this is to use an HTML body. I always receive errors no matter which way I format the .HTMLbody code. (a lot of online example codes have been trialed). I receive a syntax error with the htmlbody portion below. Can anyone help me with the HTMLbody?

I need the rental_amount from Worksheets("Status of Review").Range("u3").Value to be bold and underlined in the email body. Also if possible, is there a way to display your default signature in this email? Many users will be using this worksheet so I would like it to pull the users own signature. I have found different options online but a lot of them involve saving your default signature somewhere, which unfortunately my work will not let you access where this signature is saved. Perhaps that means there is not a work around for displaying a signature?

Code:
Sub AttachActiveSheetPDF_Title()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, title As String
  Dim OutlApp As Object
  Dim rental_amount As String
  
  
  rental_amount = Worksheets("Status of Review").Range("u3").Value
  
  Application.ScreenUpdating = False
  
 Worksheets("Form Request").Range("b1").Value = "Items Needed"

  ' Not sure for what the Title is
  title = "Documents List"
 
  ' Define PDF filename
  PdfFile = "Documents List"
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & ".pdf"
 
  ' Export activesheet as PDF
  With Worksheets("Form Request")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
 

    ' Prepare e-mail
    .BodyFormat = olFormatHTML
    .Subject = "Items Needed" & " - " & Worksheets("Status of Review").Range("O5").Value & " - " & Worksheets("Status of Review").Range("P10").Value
    .To = " " ' <-- Put email of the recipient here
    .CC = " " ' <-- Put email of 'copy to' recipient here
    .HTMLBody =  "<font face=""calibri"" style=""font-size:11pt;""> & _
"I am the agent assigned to this file. Please forward me the items below: & "<br>" & _
" 1) Lease agreements. Rental amount totaling:" & rental_amount & "<br> & _
" 2) 2 months of bank statements" & "<br> & _
" 3) w2's for the last two years. & "<br> & _
 "Thank you," & _
 .htmlbody & "</font>"
.Display
    
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
      Else
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
Can anyone help me with the HTMLbody?

I need the rental_amount from Worksheets("Status of Review").Range("u3").Value to be bold and underlined in the email body.




Code:
    ' Prepare e-mail
    .BodyFormat = olFormatHTML
    .Subject = "Items Needed" & " - " & Worksheets("Status of Review").Range("O5").Value & " - " & Worksheets("Status of Review").Range("P10").Value
    .To = " " ' <-- Put email of the recipient here
    .CC = " " ' <-- Put email of 'copy to' recipient here
    .HTMLBody =  " & _
"I am the agent assigned to this file. Please forward me the items below: & "
" & _
" 1) Lease agreements. Rental amount totaling:" & rental_amount & "
 & _
" 2) 2 months of bank statements" & "
 & _
" 3) w2's for the last two years. & "
 & _
 "Thank you," & _
 .htmlbody & ""
.Display


Does this work?






Apologies for the image - trying to paste HTML into a page that converts HTML wouldn't work!!

Let me know if that works.

Basically, it's just a string and it uses HTML tags.
 

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
Perhaps that means there is not a work around for displaying a signature?

You could try the following three adjustments to see if it triggers the signature.. but it's a shot in the dark TBH.


Code:
Sub AttachActiveSheetPDF_Title()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, title As String
  Dim OutlApp As Object
  Dim rental_amount As String
[SIZE=4][B]  Dim Signature as string [/B][/SIZE]
  
  
  rental_amount = Worksheets("Status of Review").Range("u3").Value
  
  Application.ScreenUpdating = False
  
 Worksheets("Form Request").Range("b1").Value = "Items Needed"

  ' Not sure for what the Title is
  title = "Documents List"
 
  ' Define PDF filename
  PdfFile = "Documents List"
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & ".pdf"
 
  ' Export activesheet as PDF
  With Worksheets("Form Request")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
 
            With OutlApp.CreateItem(0) 
[SIZE=4][B]    Signature = .body[/B][/SIZE]



    ' Prepare e-mail
    .BodyFormat = olFormatHTML
    .Subject = "Items Needed" & " - " & Worksheets("Status of Review").Range("O5").Value & " - " & Worksheets("Status of Review").Range("P10").Value
    .To = " " ' <-- Put email of the recipient here
    .CC = " " ' <-- Put email of 'copy to' recipient here

[SIZE=4][B]    .HTMLBody =  (Put the text as shown in the image here)  & vbcrlf & vbcrlf & Signature[/B][/SIZE]

    .Display
    
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
      Else
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,787
Messages
5,470,781
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top