Hyperlink to email, attach file, add subject line and body

[paun_shotts]

Hi,
I have a workbook for each of our suppliers, part of the workbook is a log of every order placed with the supplier, and the PO#, $ value, dates etc.
In Cell T1, I have a hyperlink that opens a new email in outlook, and auto fills in the receiver details.
What I want to do, is as the log is updated (a new order has been placed with the supplier) I need to email a pdf document with the email, to the supplier.

So I would like to automatically attached the pdf document to the email, the document is always stored in the same folder location, lets just call it "C:\Users\spotts\Documents\"
The document name will always start with "PO " and then will be the value of the last cell in column B, in the example the file name would be "PO ADP482.pdf"
I want the subject line to be "PO " and then the last value from column B, making it "PO ADP482"
Then I also want the body of the email to say:
"Hi,
Please find attached purchase order ADP482 for processing.
Thank you,"
I dont want to automatically send the email, just have the file attached, subject filled out, body filled out, and then have it on screen for me to review before pressing send.

Below is the minisheet, and thank you in advanced for your help.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	#	PO#	# Items	# B/O Items	B/O Value	B/O Item #	Order Date	Order Value	Date first part rec	Days Taken (PART)	Date Rec in Full	Days Taken (FULL)	MY Processing Days	Notes	SUPPLIER Processing Days	Ship Date	Delivery Date	Days Taken	CONTACT NAMES	CONTACT FOR PO	FREIGHT COMPANY
2	1	ADP480	96				7/07/2023	$3,935.40		0	14/07/2023	7	1		4	11/07/2023	13/07/2023	2	Sales	sales@seller.com.au
3	2	ADP481	88				17/07/2023	$4,213.80		0	21/07/2023	4	0		1	18/07/2023	21/07/2023	3
4	3	ADP482	58				31/07/2023	$3,558.30
5
6
7
8
9
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J3	Other Type	Color scale		NO
O2:O11	Other Type	Color scale		NO
O2:O11	Other Type	Color scale		NO
R2:R11	Other Type	Color scale		NO
O2:O11	Other Type	Color scale		NO
J2:J11	Other Type	Color scale		NO
L2:M11	Other Type	Color scale		NO
J1	Other Type	Color scale		NO
L1:M1	Other Type	Color scale		NO

 

[paun_shotts]

I have the below code, which seems to be working in the terms of creating the email, with recipients, subject and body, but not attaching the file. Yes, the file is present in the folder, I have checked 100 times.

The body of the email reads:

C:\Users\spotts\Documents\ File name is: PO ADP482.pdf

Below is a screenshot of the pdf file sitting in the documents folder:

Sub send_single_email_with_chosen_attachment()

Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Dim Source_File As String
Dim lastRow As Long
Dim myPath As String

lastRow = Range("B1").End(xlDown).Row
myPath = "C:\Users\spotts\Documents\"

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)



Source_File = "PO " & Range("B1").End(xlDown).Value & ".pdf"
myMail.To = "testing@gmail.com"
myMail.Subject = "Check Out my File!"
myMail.Body = myPath & " File name is: " & Source_File
  If Len(Dir(Source_File)) <> 0 Then 'IF THE FILE EXISTS THEN
    myMail.Attachments.Add Source_File
    End If
myMail.Display True
'myMail.send

End Sub

 

[paun_shotts]

Could it be done better with Application.FileSearch?
Im not sure on this one..
Please help someone.

 

[paun_shotts]

SOLVED - Was simply missing a "\" somewhere in the code. Cant remember exactly but issue is resolved.