auto email worksheet

  • Thread starter Thread starter Norm
  • Start date Start date
N

Norm

Is there a macro I can write that would email my worksheet 6 days a week (no
Sunday)

Cheers!!!
 
Use Windows Scheduled Tasks to run your macro at specific times/days.
The basics of the macro would be something like this....

'/========================================/
' Sub Purpose: example of how to send a file attachment
' Requires reference to Microsoft Outlook
'/========================================/
'
Public Sub SendTheEmail()
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objAttachments As Outlook.Attachments
Dim strTo As String, strSubject As String
Dim strBody As String
Dim strAttachment As String
Dim strCC As String, strBCC As String

On Error GoTo err_Sub

' - - - V A R I A B L E S - - - - - - - - -
strAttachment = "C:\Temp\Test.txt"
strTo = "(e-mail address removed)"
strSubject = "This is a Test of the Subject Area..."
strBody = "This is where you have the Body of the eMail"
strCC = "" 'CC:
strBCC = "" 'Blind CC:
' - - - - - - - - - - - - - - - - - - - - -

Set olApp = CreateObject("Outlook.Application")
Set objMail = olApp.CreateItem(olMailItem)
Set objAttachments = objMail.Attachments

objAttachments.Add strAttachment
objMail.Subject = strSubject
objMail.To = strTo
objMail.Body = strBody
objMail.CC = strCC
objMail.BCC = strBCC

'view the email prior to sending
' objMail.Display

'Send the email without displaying first
objMail.Send

exit_Sub:
On Error Resume Next
Set objAttachments = Nothing
Set objMail = Nothing
Set olApp = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: SendTheEmail - " & Now()
GoTo exit_Sub

End Sub
'/========================================/
 
Thanks Gary,

One problem... we use Lotus Notes
can I just replace LotusNotes where it says Outlook?
 
You'll have to add a reference to LotusNotes in your VBE and use that object.
I don't know if the rest will work with LotusNotes but you can give it a try.
Good Luck.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
 
Back
Top