Excel 2007 VBA - include the contents of a cell in a email subject line

andyg66

New Member
Joined
Aug 17, 2010
Messages
6
Hi,
I have the following code that attaches my file to and email but i want it to include the contents of three cells in the subject line and possibly in the main body. One cell is a number wich includes the / symbol (10/0) one cell is a number (098) and the third is a company name all different numbers each time, i want the subject to read "10/0098 Company Name Event Form" and the main body to read "Please find attached company name event fom, ref 10/0098" Bold text to show the cells not needed in the email.

Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Event Form"
.Body = "Please find attached a new or updated event form"
.Attachments.Add ActiveWorkbook.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Send" to
' display the mail.
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
.Subject = Range("A1").Text & Range("A2").Text & " " & Range("A3").Text & " Event Form"
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Oops, and the other bit

Code:
.Body = "Please find attached " & Range("A3").Text & " event form, ref " & Range("A1").Text & Range("A2").Text
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

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
Top