bubbazgirl1
New Member
- Joined
- Aug 31, 2011
- Messages
- 3
Hello -
I am new to VBA and I am attempting to create VBA to send an email if the user selects Yes for a vbQuestion. I was able to have an email created and sent using the code below; however, the email still sends even if the user selects no. I am not sure where the code is going off the path.
' Request to send email to management with revised metrics information
Dim response3 As VbMsgBoxResult
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
response3 = MsgBox("Do you wish to send email with attachments to management?", vbQuestion + vbYesNo, "Email Managment")
If response2 = vbYes Then
Email_Subject = "Test - Weekly Metrics " & Format(Date, "mm/dd/yyyy")
Email_Send_From = "person@gmail.com"
Email_Send_To = "person@gmail.com"
Email_Cc = "person@gmail.com"
Email_Bcc = "person@gmail.com"
Email_Body = "Hello - " & vbCr & vbCr & "I have attached the most recent version of the weekly metrics." & vbCr & vbCr & "Thanks," & vbCr & vbCr & "Jane"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.attachments.Add ("G:\***\*** " & Format(Date, "mm.dd.yyyy") & ".xlsx")
.attachments.Add ("G:\***\***2 " & Format(Date, "mm.dd.yyyy") & ".xlsm")
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
' Message to advise email sent.
MsgBox "An email(s) has been succesfully sent to the appropriate managment personnel.", vbExclamation
Else
MsgBox "Email not sent to management."
End If
I am having a similar problem with the IF statement below. If no the code is still closing the application instead of just ending the SUB.
' Determine if the user would like to close Excel.
Dim response4 As VbMsgBoxResult
repsonse4 = MsgBox("Would you like to close the document?", vbQuestion + vbYesNo, "Close Document?")
If response4 = vbNo Then Exit Sub
Application.Quit ' Close Excel completely
End Sub
I would appreciate any assistance anyone could provide!!
Thanks!
I am new to VBA and I am attempting to create VBA to send an email if the user selects Yes for a vbQuestion. I was able to have an email created and sent using the code below; however, the email still sends even if the user selects no. I am not sure where the code is going off the path.
' Request to send email to management with revised metrics information
Dim response3 As VbMsgBoxResult
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
response3 = MsgBox("Do you wish to send email with attachments to management?", vbQuestion + vbYesNo, "Email Managment")
If response2 = vbYes Then
Email_Subject = "Test - Weekly Metrics " & Format(Date, "mm/dd/yyyy")
Email_Send_From = "person@gmail.com"
Email_Send_To = "person@gmail.com"
Email_Cc = "person@gmail.com"
Email_Bcc = "person@gmail.com"
Email_Body = "Hello - " & vbCr & vbCr & "I have attached the most recent version of the weekly metrics." & vbCr & vbCr & "Thanks," & vbCr & vbCr & "Jane"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.attachments.Add ("G:\***\*** " & Format(Date, "mm.dd.yyyy") & ".xlsx")
.attachments.Add ("G:\***\***2 " & Format(Date, "mm.dd.yyyy") & ".xlsm")
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
' Message to advise email sent.
MsgBox "An email(s) has been succesfully sent to the appropriate managment personnel.", vbExclamation
Else
MsgBox "Email not sent to management."
End If
I am having a similar problem with the IF statement below. If no the code is still closing the application instead of just ending the SUB.
' Determine if the user would like to close Excel.
Dim response4 As VbMsgBoxResult
repsonse4 = MsgBox("Would you like to close the document?", vbQuestion + vbYesNo, "Close Document?")
If response4 = vbNo Then Exit Sub
Application.Quit ' Close Excel completely
End Sub
I would appreciate any assistance anyone could provide!!
Thanks!