Auto Emailing

Ben, here is a comprehensive and tested email code using Outlook, set up within the context of a VB Yes, No, Cancel button (if you want to review, send, or cancel), and a password (so not just anyone can email the file). You can assign this code to a command button or extricate the portion(s) you want and place into your existing code. Modify body text as desired.

Tom Urtis

Sub Email()
' Email Macro
‘Macro by Thomas Urtis

'Input box to verify password

Dim myPassword As String

myPassword = InputBox(prompt:="Please enter the password to proceed:", _
Title:="Password is required to auto-email this file.")

If myPassword <> "Password" Then
MsgBox prompt:="Click OK to return to Report.", _
Title:="Cancelled -- correct password not entered", _
Buttons:=16

Else
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)

End If

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 = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = “MarySmith@anywho.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 = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = “MarySmith@anywho.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

otlApp.Quit

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

woah! thats far to complicated! Its for my gcse and ineed to be able to explain it. Is there an easy was just to set up a smiple code? i can do it for everything just not the body text of the e-mail. Cheerz
ben

OK, here's the pared-down version. Your existing code without the body text may not be compatible with the body text line in the With structure of this code suggestion. Because I cannot see your existing code here is an entire code set-up for just sending an Outlook email with an attachment that has worked well for me.

By way of explanation, the first block of code is dimensionalizing and setting objects.

The second block of code (in the form of a "With structure") provides for the user to enter data as per the fields in an Outlook screen.

The third block of code quits Outlook, and resets it to conserve system memory.

Hope this helps.

Tom Urtis

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 = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = “MarySmith@anywho.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

otlApp.Quit

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing