Found this code in your achieves - works like a charm.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o></o>
2 Questions:<o></o>
<o></o>
1. I have a worksheet that I would like to copy and paste into the body of the email as a picture - could vary in row numbers.
Any suggestions?<o></o>
<o></o>
2. Rather than running the macro I would like to auto run based on the answer "yes" in a specific cell.<o></o>
<o></o>
Not my area of expertise so your help is appreciated. Thanks<o></o>
Sub Email()
' Email Macro
' Macro by Thomas Urtis
Dim Resp As Integer
Resp = MsgBox(prompt:="Click Yes to review email, No to immediately send, or Cancel.", _
Title:="Email options: Want to review email before sending?", _
Buttons:=3 + 32)
Select Case Resp
'Yes was clicked, user wants to review email first
Case Is = 6
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "jane.doe@anywhere.com"
.CC = "jane.doe@anywhere.com"
.Subject = "email from me"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
.Attachments.Add fName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'If no is clicked
Case Is = 7
Dim myOutlok As Object
Dim myMailItm As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "jane.doe@anywhere.com"
.CC = "jane.doe@anywhere.com"
.Subject = "email from me"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
.Attachments.Add fName
.Send
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'If Cancel is clicked
Case Is = 2
MsgBox prompt:="Click OK to return to Report.", _
Title:="EMAIL CANCELLED", _
Buttons:=64
End Select
End Sub
fficeffice" /><o></o><o
></o>2 Questions:<o
></o><o
></o>1. I have a worksheet that I would like to copy and paste into the body of the email as a picture - could vary in row numbers.
Any suggestions?<o
></o><o
></o>2. Rather than running the macro I would like to auto run based on the answer "yes" in a specific cell.<o
></o><o
></o>Not my area of expertise so your help is appreciated. Thanks<o
></o>Sub Email()
' Email Macro
' Macro by Thomas Urtis
Dim Resp As Integer
Resp = MsgBox(prompt:="Click Yes to review email, No to immediately send, or Cancel.", _
Title:="Email options: Want to review email before sending?", _
Buttons:=3 + 32)
Select Case Resp
'Yes was clicked, user wants to review email first
Case Is = 6
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "jane.doe@anywhere.com"
.CC = "jane.doe@anywhere.com"
.Subject = "email from me"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
.Attachments.Add fName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'If no is clicked
Case Is = 7
Dim myOutlok As Object
Dim myMailItm As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "jane.doe@anywhere.com"
.CC = "jane.doe@anywhere.com"
.Subject = "email from me"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
.Attachments.Add fName
.Send
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'If Cancel is clicked
Case Is = 2
MsgBox prompt:="Click OK to return to Report.", _
Title:="EMAIL CANCELLED", _
Buttons:=64
End Select
End Sub
