Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
You'd have to use
.Attachments.Add ActiveWork.FullName
Macro to create e mail and attach file
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
I have written a macro which I pulled together from various other posts to create an e mail, I now want to attach the activeworkbook to the message but can't seem to figure out the code. Can anyone help?
This is what I have so far
Sub Send_RFI()
Application.ActiveWorkbook.Save
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "<font size=""4"" face=""Arial"">" & _
"Dear ,<br><br>" & _
"Please can you fill out the attached Sub-contract factory time table for the above project.<br><br>" & _
"Please can you return the form by the " & _
Range("return_date") & _
"<br><br>Any questions please contact me.<br><br>" & _
"Many Thanks"
On Error Resume Next
With OutMail
.attachments = ActiveWorkbook
.To = ""
.CC = ""
.BCC = ""
.Subject = "Request for SC Routings for " & _
Range("order_number") & ", " & Range("title")
.HTMLBody = strbody
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub
Microsoft 365 and Office | Excel | For home | Windows
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
1 additional answer
Sort by: Most helpful
-
Anonymous
2011-10-21T12:25:19+00:00 'See my inline comments
Sub Send_RFI()
Application.ActiveWorkbook.Save
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
'Workbook.Path gives the name of the workbook and is empty till it is saved for the first time
If ActiveWorkbook.Path <> "" Then
'Create and instantiate Outlook Application Object and create a mail item
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Create mail body
strbody = "<font size=""4"" face=""Arial"">" & _
"Dear ,<br><br>" & _
"Please can you fill out the attached Sub-contract factory time table for the above project.<br><br>" & _
"Please can you return the form by the " & _
Range("return_date") & _
"<br><br>Any questions please contact me.<br><br>" & _
"Many Thanks"
On Error Resume Next
'Using the mail object
With OutMail
'Add active workbook as attachment
.attachments = ActiveWorkbook
' To/CC/BCC are left blank, you can fill as you want
.To = ""
.CC = ""
.BCC = ""
.Subject = "Request for SC Routings for " & _
'This should give the order number and title... not sure it will work as it is
Range("order_number") & ", " & Range("title")
.HTMLBody = strbody
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub
'Hope this helps