VBA to add signature and comma as line break

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
66
Hi All,
I'm converting Excel sheet to PDF and send via email (outlook). I have made this to email, but I'm unable to work out how to add my signature and also my "Body" has comma and don't know how to line break it.
Can someone help me with this VBA code? or what am I doing wrong?

VBA Code:
Sub SendExcelFilesAsPDF()

  Dim OutlookApp As Outlook.Application
  Dim emItem As Object
  Dim Recipient As String, Subject As String
  Dim Message As String, Fname As String
   Dim x As Date
 x = Format(Now() + 1, "MMMM dd, yyyy")
 
  ' Message details
  Recipient = "kitsas@erectsafe.com.au"
  Subject = Range("K13") & " " & x
  'Message = Range("F19")
  Message = Range("K14").Value
  Fname = Range("F19") & ".pdf"
 
  ' Create the PDF attachment
  ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, Filename:=Fname
 
  ' Create Outlook object
  Set OutlookApp = New Outlook.Application
 
  ' Create email Item, display or send it
  Set emItem = OutlookApp.CreateItem(olMailItem)
  With emItem
   .To = Recipient
   .Subject = Subject
   .HTMLBody = Message & .HTMLBody
   .Attachments.Add Fname
   .Display
  
End With
 Set OutlookApp = Nothing
 
 
 
 
 
End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
145
Office Version
  1. 2010
Platform
  1. Windows
How about
VBA Code:
Dim EMtext As String
EMtext = "Just line" & vbNewLine & "Other line" & vbNewLine & vbNewLine & "My signature"
emItem.Body = EMtext
 

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
66
How about
VBA Code:
Dim EMtext As String
EMtext = "Just line" & vbNewLine & "Other line" & vbNewLine & vbNewLine & "My signature"
emItem.Body = EMtext
Hi LazyBug,
Sorry but this didn't work or I'm doing something wrong.
I have cell "K14" which has "Hi Peter Homes, As attached is the quote you have requested". When this converts into the email, it's doesn't do a line break on "Comma".
Also my signature doesn't still come up?
 

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
145
Office Version
  1. 2010
Platform
  1. Windows
Do you want to break a line after every comma?
VBA Code:
Message = Range("K14").Value
Message = Replace(Message, ",", "," & vbNewLine)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,005
Messages
5,575,520
Members
412,672
Latest member
Tupelo1984
Top