PDA

View Full Version : [SOLVED] Sending automatic emails



Kicker
02-05-2015, 01:14 PM
From an Excel spreadsheet, how do I create and send emails automatically.

I have an application that uses a spreadsheet and it needs to send out emails once daily. I can open the workbook using the windows scheduler and automatically run a macro, but am having a little trouble sending emails.

Amy help would be appreciated.

Blade Hunter
02-05-2015, 02:20 PM
This is my save and send routine.



Dim ArchiveFolder As String, ArchiveFileName As String

Private Sub SaveCopy()
ArchiveFolder = "C:\Reporting Archive\"
ArchiveFileName = "YourFileName - " & Format(Now, "YYYYMMDD")
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=ArchiveFolder & ArchiveFileName & ".xlsx" ', FileFormat:=xlExcel8
Sheets(Array("Summary - Volume", "Summary - Orders")).Select 'Next 3 lines export some tabs as PDF also
Sheets("Summary - Volume").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=ArchiveFolder & ArchiveFileName & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Application.DisplayAlerts = True
ActiveWindow.Close
EmailCopy
End Sub




Private Sub EmailCopy()
Dim oApp, oMail As Object
Dim WB As Workbook
Dim FileName As String, BodyText As String
Application.ScreenUpdating = False
FileName = ArchiveFolder & ArchiveFileName
On Error Resume Next
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
BodyText = "Your email text"
.To = "Email1@somewhere.com;Email2@somewhere.com"
.Subject = "Daily Report for:" & Format(Now, "DD/MM/YYYY")
.Body = BodyText
.Attachments.Add FileName & ".xlsx"
.Attachments.Add FileName & ".pdf" 'Add the PDF also
.Display
.Send
End With
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub