email through excel

[Nichole09]

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?

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

 

[MartyS]

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.

    ' 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]

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.

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