J
Jenny B.
Good Afternoon,
I have a Macro I use to send an email to a group after a spreadsheet has
been completed. I’m looking to incorporate another send piece to the
existing routine and was wondering if someone could offer some advice.
The first email is programmed to always send just a message to particular
person after completion - Sub SalesandLisaEmail. The Second Macro (MySend2)
is set-up to send a copy of the Active Worksheet to a different person. I’m
looking to change this up so both run at the same time vs. each running one
at a time. I’m also looking to change the send to on the Second Macro and
have it send the copy to the User vs. the a static email address (i.e.,
..Value = Environ(" username").
Lastly, is there a way to write additional logic in the routine which will
remove the Macros from the Worksheet being sent to the user?
Thanks in advance for your review and thoughts – Jenny B.
Sub SalesandLisaEmail()
On Error GoTo IndicateError
Dim Dockwkbk As Workbook
Set Dockwkbk = Workbooks("Account Entry Database BETA.xls")
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
Mitem.To = "(e-mail address removed)"
Mitem.Subject = "New Statement Checklist" & " - " &
Dockwkbk.Sheets("Account Mgmt Checklist").Range("F5").Value
Mitem.body = "A New Checklist has been created for review." & vbNewLine &
vbNewLine & "Please review and forward to the DMS Group when complete." _
& vbNewLine & vbNewLine & "Thank you - Account Management."
Mitem.SEND
Set OLook = Nothing
Set Mitem = Nothing
Exit Sub
IndicateError:
MsgBox "Your email message failed. Please select Finished again."
Resume Next
End Sub
Sub MySend2()
ThisWorkbook.Sheets("Account Mgmt Checklist").Copy
With ActiveWorkbook
.SendMail Recipients:="(e-mail address removed)", _
Subject:="Copy of Checklist" & " " & Range("F5").Value & " " &
Format(Date, "mm/dd/yy")
.Close SaveChanges:=False
End With
End Sub
I have a Macro I use to send an email to a group after a spreadsheet has
been completed. I’m looking to incorporate another send piece to the
existing routine and was wondering if someone could offer some advice.
The first email is programmed to always send just a message to particular
person after completion - Sub SalesandLisaEmail. The Second Macro (MySend2)
is set-up to send a copy of the Active Worksheet to a different person. I’m
looking to change this up so both run at the same time vs. each running one
at a time. I’m also looking to change the send to on the Second Macro and
have it send the copy to the User vs. the a static email address (i.e.,
..Value = Environ(" username").
Lastly, is there a way to write additional logic in the routine which will
remove the Macros from the Worksheet being sent to the user?
Thanks in advance for your review and thoughts – Jenny B.
Sub SalesandLisaEmail()
On Error GoTo IndicateError
Dim Dockwkbk As Workbook
Set Dockwkbk = Workbooks("Account Entry Database BETA.xls")
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
Mitem.To = "(e-mail address removed)"
Mitem.Subject = "New Statement Checklist" & " - " &
Dockwkbk.Sheets("Account Mgmt Checklist").Range("F5").Value
Mitem.body = "A New Checklist has been created for review." & vbNewLine &
vbNewLine & "Please review and forward to the DMS Group when complete." _
& vbNewLine & vbNewLine & "Thank you - Account Management."
Mitem.SEND
Set OLook = Nothing
Set Mitem = Nothing
Exit Sub
IndicateError:
MsgBox "Your email message failed. Please select Finished again."
Resume Next
End Sub
Sub MySend2()
ThisWorkbook.Sheets("Account Mgmt Checklist").Copy
With ActiveWorkbook
.SendMail Recipients:="(e-mail address removed)", _
Subject:="Copy of Checklist" & " " & Range("F5").Value & " " &
Format(Date, "mm/dd/yy")
.Close SaveChanges:=False
End With
End Sub