How to use VBA to send Email (outlook) with title of workbook
Category: [Excel] Demo Available
How to use VBA code to obtain date from cell, then calculate 3 months later
Solution:
The user is looking for the correct line of code that when button is clicked it will take the name of the workbook file the user is using and in the SUBJECT line of the email show " thefilename.xxx is now prepared for review".
BTW with the code I am using below, sending the email works perfectly upon button click. Just hoping to add the file name to subject.
Code:
Private Sub sendEmailtoIC_Click()
On Error GoTo ErrHandler
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.To = "[email protected]"
.cc = "[email protected]"
.cc = "[email protected]"
.Subject = "thefilename Report Prepared for Review"
.Body = ""
.Send
End With
' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End Sub
Requirement:
Code:
Option Explicit
Sub EmailSend()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody1 As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = ""
.CC = ""
.BCC = ""
.Subject = Application.ActiveWorkbook.Name & " Report Prepared for Review"
.Body = "Hi"
.Display
'.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Logit.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to extract multiple emails separated with semicolon and brackets |
| How to import data in a specific sheet from another workbook (sheet number must be variable) |
| How to create new workbook by copying rows from multiple sheets based on value in column A |
| How to copy/paste between workbooks with relative referencing |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.