Snowsheep91
New Member
- Joined
- Oct 6, 2014
- Messages
- 4
Hi there,
I'm creating a spreadsheet for work which documents which data we purchase against which projects. Currently all the info gets filled in and then I've created a simple macro with a command button which sends the updated spreadsheet to our finance department. They then have to check to see which was the latest project added to the spreadsheet.
What I want to do is not just create the email, but in the subject line along with the subject 'New Purchase Order', add in the project number which is referenced in the H column. This would be easy for me if it was just one cell (Range "H7") but I want it to pick up the last cell with text in it in that column. Does that make sense?
This is the current VBA:
Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "xyz@zyx.com"
.CC = ""
.BCC = ""
.Subject = "New Purchase Order"
.Body = "Hi XYZ," & vbNewLine & vbNewLine & _
"Please find attached the latest version of the purchase order sheet." & vbNewLine & vbNewLine & "Thanks,"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Quick replies appreciated! I'm not very good at VBA so actual codes would be appreciated
Thanks,
SS91
I'm creating a spreadsheet for work which documents which data we purchase against which projects. Currently all the info gets filled in and then I've created a simple macro with a command button which sends the updated spreadsheet to our finance department. They then have to check to see which was the latest project added to the spreadsheet.
What I want to do is not just create the email, but in the subject line along with the subject 'New Purchase Order', add in the project number which is referenced in the H column. This would be easy for me if it was just one cell (Range "H7") but I want it to pick up the last cell with text in it in that column. Does that make sense?
This is the current VBA:
Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "xyz@zyx.com"
.CC = ""
.BCC = ""
.Subject = "New Purchase Order"
.Body = "Hi XYZ," & vbNewLine & vbNewLine & _
"Please find attached the latest version of the purchase order sheet." & vbNewLine & vbNewLine & "Thanks,"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Quick replies appreciated! I'm not very good at VBA so actual codes would be appreciated
Thanks,
SS91