VBA to add signature and comma as line break

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
111
Office Version
  1. 365
  2. 2016
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
How about
VBA Code:
Dim EMtext As String
EMtext = "Just line" & vbNewLine & "Other line" & vbNewLine & vbNewLine & "My signature"
emItem.Body = EMtext
 
Upvote 0
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?
 
Upvote 0
Do you want to break a line after every comma?
VBA Code:
Message = Range("K14").Value
Message = Replace(Message, ",", "," & vbNewLine)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top