Macro to save a Workbook and then e-mail that file from Outlook

[Toby019#]

Hi,

I am not an experienced user as yet but have combined some code from various post to try and solve my problem.

I want to save an Excel Workbook into a specific folder on the c Drive with a date attached to a fixed file name. I then want to export that specific file in Outlook to an e-mail address.

The Code I am using fails on the line "Attachments.Add ActiveWorkbook.FullName"

Hope someone can help me solve this.

.

Sub ExportOffice3()
'
' ExportOffice3 Macro


Dim dt As String, wbNam As String, path As String
path = "C:\Daily Site Diary\Weekly Site Records\"
wbNam = "Weekly Site Record_"
dt = Format(CStr(Now), "dd_mm_yyyy")
ActiveWorkbook.SaveAs Filename:=path & wbNam & dt & ".xlsm"


Dim OutlookApp As Object
Dim OutlookMailItem As Object
Dim myAttachments As Object

Set OutlookApp = CreateObject("OutLook.Application")
Set OutlookMailItem = OutlookApp.CreateItem(0)
Set myAttachments = OutlookMailItem.Attachments

With OutlookMailItem
.To = "info@civil-contracting.com.au"
.Subject = "Weekly Site Record"
.Body = "Please find Attached the Weekly Site Record in Excel format."
Attachments.Add ActiveWorkbook.FullName
'send
.Display
End With


Set OutlookMailItem = Nothing
Set OutlookApp = Nothing

'
End Sub

[bakerman2]

Please amend your 1st post with Code Tags when posting code !!

Try this way.

Sub ExportOffice3()
'
' ExportOffice3 Macro

Dim dt As String, wbNam As String, path As String

path = "C:\Daily Site Diary\Weekly Site Records\"
wbNam = "Weekly Site Record_"
dt = Format(CStr(Now), "dd_mm_yyyy")
ActiveWorkbook.SaveAs Filename:=path & wbNam & dt & ".xlsm"

With CreateObject("OutLook.Application").CreateItem(0)
    .To = "info@civil-contracting.com.au"
    .Subject = "Weekly Site Record"
    .Body = "Please find Attached the Weekly Site Record in Excel format."
    .Attachments.Add path & wbNam & dt & ".xlsm"
    'send
    .Display
End With

End Sub

[Toby019#]

Thank you. That fixed the problem. Great work

[bakerman2]

You're welcome and thanks for rep+.

If this answers your question please mark the thread as Solved.