abonnette86
New Member
- Joined
- Feb 21, 2020
- Messages
- 10
- Office Version
- 2013
- Platform
- Windows
All,
I have been tasked with something that I am having trouble with. I need to create a VBA in Outlook that automatically sends an email every Friday @ 12PM - not a reminder to send an email, but a code that physically sends the email without any end user intervention.
1. It has to be sent if I'm logged in or not, or if Outlook is even open (AKA doesnt matter if I have that friday off or not, the email still gets sent)
2. The recipients are at separate locations, so I'd like each location to get it's own 'version' of the email using an excel file to control the info that goes out. (AKA the VBA triggers a separate email for each line. some locations I might want the body to say this not that, add/remove people, etc.). Rows in the excel file may be added or subtracted.
3. I would like the option to attach a different excel file saved to my desktop.
So far this is what I have. It worked by creating a new category and assigning a new appointment to that category, and setting the desired recurrence. I realize the code I have is no where near where it needs to be to achieve what I want, but thats why I need help.
Private Sub Application_Reminder(ByVal Item As Object)
Dim objMsg As MailItem
Dim objApp As AppointmentItem
Dim Att As Attachment
Dim tmpFolder As String
Dim filePath As String
Set objMsg = Application.CreateItem(olMailItem)
If Item.MessageClass <> "IPM.Appointment" Then
Exit Sub
End If
If Item.Categories <> "Recurring Email" Then
Exit Sub
End If
tmpFolder = Environ("TEMP")
For Each Att In Item.Attachments
filePath = tmpFolder & "\" & Att.Filename
Att.SaveAsFile (filePath)
objMsg.Attachments.Add filePath
Kill filePath
Next Att
objMsg.To = Item.Location
objMsg.Subject = Item.Subject
objMsg.Body = Item.Body
objMsg.Send
Set objMsg = Nothing
End Sub
I have been tasked with something that I am having trouble with. I need to create a VBA in Outlook that automatically sends an email every Friday @ 12PM - not a reminder to send an email, but a code that physically sends the email without any end user intervention.
1. It has to be sent if I'm logged in or not, or if Outlook is even open (AKA doesnt matter if I have that friday off or not, the email still gets sent)
2. The recipients are at separate locations, so I'd like each location to get it's own 'version' of the email using an excel file to control the info that goes out. (AKA the VBA triggers a separate email for each line. some locations I might want the body to say this not that, add/remove people, etc.). Rows in the excel file may be added or subtracted.
VBA Event triggers outgoing email for each line: | Location | objMsg.To = | objMsg.CC = | objMsg.BCC = | objMsg.Subject = | objMsg.Body = |
Outgoing Email 1 | Place 1 | |||||
Outgoing Email 2 | Place 2 | |||||
Outgoing Email 3 | Place 3 |
So far this is what I have. It worked by creating a new category and assigning a new appointment to that category, and setting the desired recurrence. I realize the code I have is no where near where it needs to be to achieve what I want, but thats why I need help.
Private Sub Application_Reminder(ByVal Item As Object)
Dim objMsg As MailItem
Dim objApp As AppointmentItem
Dim Att As Attachment
Dim tmpFolder As String
Dim filePath As String
Set objMsg = Application.CreateItem(olMailItem)
If Item.MessageClass <> "IPM.Appointment" Then
Exit Sub
End If
If Item.Categories <> "Recurring Email" Then
Exit Sub
End If
tmpFolder = Environ("TEMP")
For Each Att In Item.Attachments
filePath = tmpFolder & "\" & Att.Filename
Att.SaveAsFile (filePath)
objMsg.Attachments.Add filePath
Kill filePath
Next Att
objMsg.To = Item.Location
objMsg.Subject = Item.Subject
objMsg.Body = Item.Body
objMsg.Send
Set objMsg = Nothing
End Sub