Sending an email from Excel

[colinharwood]

Hi
I use the following code to save a worksheet as a pdf file and then attach it to an email to send.
I am trying to get the name colin, which is in in bold type, in the content= line to be the value from the cell S3 on the worksheet, but cannot get it to work.
Is there a way to do this
Thanks

Dim ThisFile As String
Dim FileDrive As String

CreateObject("WScript.Network").SetDefaultPrinter "Microsoft Print to PDF"

Sheets("PDF Membership Card").Range("A1:H45").Select
ThisFile = Range("S3").Value
FileDrive = "D:\Tonbridge MES\"
Selection.PrintOut Copies:=1, ActivePrinter:= _
"Microsoft print to pdf", Collate:=True, _
printtofile:=True, prtofilename:=FileDrive & ThisFile & ".pdf"
Sheets("PDF Membership Card").Range("A1").Select

CreateEmail:

' Create Email to someone
sCmd = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe"
Email = Range("S4").Value 'email address
Subject = "PDF Membership Card"
Content = "Hi%20Colin%0A%0APlease%20find%20attached%20a%20PDF Membership Card%20%0A%0ARegards%0A%0AAdrian Banks%0A%0ATreasurer"
Attch = "D:\Tonbridge MES\" & ThisFile & ".pdf" ' file name and path
sCmd = sCmd & " -compose " & "to=" & Email
sCmd = sCmd & ",subject=" & Subject
sCmd = sCmd & ",attachment=" & Attch
sCmd = sCmd & ",body=" & Content
Call Shell(sCmd, vbNormalFocus)

End Sub

 

[severynm]

Try

Content = "Hi%20" & SheetName.Range("S3").Value & "%0A%0APlease%20find%20attached%20a%20PDF Membership Card%20%0A%0ARegards%0A%0AAdrian Banks%0A%0ATreasurer"

substituting SheetName for whatever your worksheet is called.

 

[colinharwood]

Thanks for the suggestion.
I have already tried that, and I cannot get it to work, I suspect that what I am trying to do is not possible.
It seems that whatever you put after the "Hi%20", just appears as text. in the email

 

[severynm]

Why does the content need to be URL encoded while the subject does not?

Also, I don't know about your email client, but if you create an email in Outlook with VBA, there's a .HTMLBody property (as opposed to .Body) which can parse more advanced formatting (HTML in this case). It's possible your client might have something similar.

 

[colinharwood]

That is the way I have to enter it to get the desired formatting
PS I use Thunderbird