vba code for email

[sgolder]

I would like to set a spreadsheet to open from outlook and email to a specific person at a certain time on a monday morning. does anyone know the code for this?

 

[DonkeyOte]

any reason why you can't do this from XL? (Why OL specifically...though yes, you can do it).

 

[DonkeyOte]

eg OL Code...set as module in Outlook VBA

Sub mail_book()

If Hour(Now()) = 10 Then

Dim aOutlook As Outlook.Application, aEmail As Outlook.MailItem

Set aOutlook = GetObject(, "Outlook.Application")

Set aEmail = aOutlook.CreateItem(olMailItem)

aEmail.Importance = olImportanceHigh

aEmail.Subject = "First ABT Reminder For - Dated " & Today
aEmail.Body = "This is a Test"
aEmail.Recipients.Add "j.logss@loggsville.com"
aEmail.Attachments.Add ("C:\MrE\1.xls")

On Error GoTo ErrorHandler
aEmail.Send

Exit Sub

ErrorHandler:

Set aEmail = aOutlook.CreateItem(olMailItem)
aEmail.Importance = olImportanceHigh
aEmail.Subject = "ABT Alert Error For Ee - Dated " & Date
aEmail.Recipients.Add "j.bloggs@bloggs.com"
aEmail.Send
Resume Next

End If

End Sub

 

[sgolder]

yeah excel would be better. do u know the code for that?

 

[DonkeyOte]

same as above :wink:

 

[sgolder]

the code wont work mate, says that user variable is not defined,

 

[DonkeyOte]

in VB Editor you need to reference the Outlook object library - Tools - References - Outlook 9.0 / 10.0 object library pending your XL version.

 

[sgolder]

how do i specify an exact time to send the email?

and how do i get it to run at that time.?

 

[DonkeyOte]

OK well there are quite a few messages here about scheduling in XL - the main approach within XL is to use the OnTime Method - however this requires that the file is open in order for the OnTime to kick in...ie. the macro will execute at a certain time.

My own preference is to use a Windows Scheduler (downloadable for free off the net) which will launch the Excel Application at a specific time (ie whe you want the email sent) , and then open a specified file - I then use a Workbook_Open event in the file - and as part of that code include some tests to determine as to whether or not I want the macro to execute fully though in this instance it's probably not necessary as you just get the scheduler to execute when you do want the mail sent....

eg

Private Sub Workbook_Open()

your code...

ActiveWorkbook.Close SaveChanges = False 'will save

End Sub

I am no expert with On Time so suggest you look around if you'd prefer that approach...I just prefer to not have files open all the time when it can be avoided.