Hi,

I am trying to create the body of an email using VBA and it doesn't seem to work. Any help is appreaciated.

Sub EmailDailyConversion()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Title of email
  Title = "Daily Conversion Report: " & Range("C3")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName & Range("C3")
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .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
    .Subject = Title
    .To = Range("DV5") & "; " & Range("DV6") & "; " & Range("DV7") & "; " & Range("DV8") ' <-- Put email of the recipient here
    .CC = Range("DW5") & "; " & Range("DW6") & "; " & Range("DW7") & "; " & Range("DW8") & "; " & Range("DW9") & "; " & Range("DW10") & "; " & Range("DW11") ' <-- Put email of 'copy to' recipient here
    .Body = vbCrLf & "Hi All," _
            & vbCrLf & vbCrLf & "Attached is the Daily Conversion Report for " & Range("C3") & "." _
            & vbCrLf & vbCrLf & "TSR/TFO Combined Conversion was " & Range("M71") & " " & Range("DT71") & " " & Range("R71") & " pp." _
            & vbCrLf & vbCrLf & "TSR Conversion was " & Range("M68") & " " & Range("DT68") & " " & Range("R68") & " pp." _
            & vbCrLf & vbCrLf & "TFO Conversion was " & Range("M69") & " " & Range("DT69") & " " & Range("R69") & " pp." _
            & vbCrLf & "      Northeast Conversion was " & Range("M23") & " " & Range("DT23") & " " & Range("R23") & " pp." _
            & vbCrLf & "      Southeast Conversion was " & Range("M34") & " " & Range("DT34") & " " & Range("R34") & " pp." _
            & vbCrLf & "      Central Conversion was " & Range("M46") & " " & Range("DT46") & " " & Range("R46") & " pp." _
            & vbCrLf & "      West Conversion was " & Range("M57") & " " & Range("DT57") & " " & Range("R57") & " pp." _
            & vbCrLf & "      Mid-Atlantic Conversion was " & Range("M66") & " " & Range("DT66") & " " & Range("R66") & " pp." _
            & vbCrLf & vbCrLf & "Regards," _
            & vbCrLf & "Store Ops."
          
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Wait (Now + TimeValue("0:00:02"))
    'Application.SendKeys ""

    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    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