Forum Discussion

Tarakh R's avatar
Tarakh R
Copper Contributor
Apr 19, 2018

VBA to send the excel from outlook with attachment

Anyone from the forum please review below code and advice corrections. When I click Accepted and send, the email was sent but there is no data in the spreadsheet. 

 

Sub Button3_Click()

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@hotmail.com"
.CC = ""
.BCC = ""
.Subject = "Approved: Costing Change Request Form"
.Body = "I am authorised or have delegation of authority to submit attached costing changes for given staff"
.Attachments.Add ActiveWorkbook.FullName

End With
Response = MsgBox("If you are sure to send an email to HR Service Centre", vbOKCancel + vbCritical, "Exiting Sub")

If Response = vbOK Then
OutMail.Send

MsgBox ("Thank you! Your request is sent to HR service centre. You will soon receive an email with ticket number for reference")

ElseIf Response = vbCancel Then

MsgBox ("Form is not submitted")

End If

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing


End Sub

No RepliesBe the first to reply

Resources