Cannot Figure Out One Thing on VBA

Status
Not open for further replies.

Alexa

New Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Hello All!

I have been working away on a project and I am at a stopping point and would love help if anyone has insight on this. Here is the current Macro that I have. This will take an appointment and when ran will create an email to remind the client of their appointment including detail. Currently, it is successfully pulling/generating the email that I want to be sent.

The issue that I am having is that I want to have the email recipient field to pull from the appointment location field (we put the email address in the location field).

If any of you might be able to help or give me suggestions it would be greatly appreciated as I cannot figure it out.

Thank you!

Sub SendMeetingReminder()
'On the next line change True to False if you want the reminder to send without giving you the chance to edit it.
'Leave it set to True if you want an opportunity to edit the message before sending it.
Const EDIT_FIRST = True
'On the next line edit the message as desired. Both %START% and %LOCATION% are parameters that will be replaced
'with the meeting start time and location respectively before the message is displayed/sent.
Const MSG_TEXT = "Hello,<br><br>You have an upcoming appointment at Accounting and Tax Services.<br><br>Start: <b>%START%</b><br>Location: <b>(Blank for Online)
</b><br><br>Please respond to this email to confirm your appointment. If you should need to make any changes or adjustments, please give us a call at (Blank for online).<br><br>Please let me know if you have any questions.<br><br>Thank you!<br><br>Team<br>(206) 241-5033"
Const MACRO_NAME = "Send Meeting Reminder"
Const ERR_MSG = "You must select an appointment for this macro to work."
Dim olkApt As Object, _
olkRem As Outlook.MailItem, _
olkRec As Outlook.Recipient
On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
Set olkApt = Application.ActiveExplorer.Selection(1)
Case "Inspector"
Set olkApt = Application.ActiveInspector.CurrentItem
End Select
If TypeName(olkApt) <> "Nothing" Then
If olkApt.Class = olAppointment Then
Set olkRem = Application.CreateItem(olMailItem)
With olkRem
.Subject = "Accounting and Tax Services - Appointment Reminder"
.HTMLBody = MSG_TEXT & .HTMLBody
.HTMLBody = Replace(.HTMLBody, "%START%", Format(olkApt.Start, "ddd, mmm d at h:mm AMPM"))
.HTMLBody = Replace(.HTMLBody, "%LOCATION%", olkApt.Location)
For Each olkRec In olkApt.Recipients
If (olkRec.Type <> olResource) And (olkRec.Name <> Session.CurrentUser.Name) Then
If Not olkRec.MeetingResponseStatus = olResponseDeclined Then
olkRem.Recipients.Add olkRec.Name
End If
End If
Next
.Recipients = "%LOCATION%"
If EDIT_FIRST Then
.Display
Else
.Send
End If
End With
Else
MsgBox ERR_MSG, vbCritical + vbOKOnly, MACRO_NAME
End If
Else
MsgBox ERR_MSG, vbCritical + vbOKOnly, MACRO_NAME
End If
On Error GoTo 0
Set olkApt = Nothing
Set olkRem = Nothing
Set olkRec = Nothing
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
is there more than one address in the location field? if no, use
.To = olkApt.Location

if yes, try something like this - use semi-colon as the address separator in the location field.
strAddress = olkApt.Location
'it might work to use the location field here, instead of converting it to a string
.to = strAddress
Set Recipients = olkRem.Recipients
Recipients.ResolveAll

If it doesn't work (i didn't test it), you'll need to use the split function to split the entries into an array and use recipients.add(i) to add them.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
A Cannot Figure Out This VBA Outlook VBA and Custom Forms 2
S Custom Field Cannot Be Displayed In Views Outlook VBA and Custom Forms 2
G Cannot Move Autocomplete File to New Computer Using Outlook 15
rubaboo The vew cannot be found Outlook VBA and Custom Forms 0
A Cannot copy this folder because it may contain private items Using Outlook 0
L Cannot open PST file for first session each day Using Outlook 6
David in Mississippi New (Feb 2020) install of Outlook 365 - Cannot specify PST for different accounts Using Outlook 0
Marc2019 Need help please! Cannot Setup my outlook email account on my Mac Outlook 2011 Using Outlook.com accounts in Outlook 2
noshairwan Registry cannot be found, Outlook Security Using Outlook 2
Marc2019 Cannot input Vietnamese Characters on my MSOutlook 2016 in Windows 7 Using Outlook 0
T Cannot remove needless PST Using Outlook 1
Marc2019 Cannot setup Outlook Account on Mac Os 10.6.8 Using Outlook 3
N pst files are recreating automaticaly and cannot be deleted Using Outlook 5
Mark Foley Cannot enable add-in in outlook 2010 Using Outlook 0
T "cannot find the calendar folder for this item" - calendar items stuck in outbox Using Outlook 0
A Created a new user account and the local account cannot see email Using Outlook 3
iwshim outlook 2013 - I cannot see the "Manage Add-ins" Using Outlook 2
T Cannot connect to main BT email account, sub accts okay Using Outlook 0
O The page that you are trying to access cannot be loaded. Using Outlook 0
M Daily Task List Minimized Cannot Display Using Outlook 2
geofferyh Cannot get Macro to SAVE more than one message attachment??? Outlook VBA and Custom Forms 5
DariTrevino MIcrosoft Outlook PST file cannot repair Using Outlook 1
B One email cannot be sent or received (but leaves outbox!) Using Outlook 1
C Outlook 2016 cannot delete, email showing in root Exchange Server Administration 5
S Outlook.com cannot edit some contacts Using Outlook.com accounts in Outlook 5
B Search: Cannot find which Folder Contains a Message Using Outlook 3
C Outlook - cannot save subject line changes Using Outlook 2
M cannot change delivery folders with IMAP accounts Using Outlook 0
S Appointment-Cannot set Categories because ConversationID is not set Outlook VBA and Custom Forms 1
Fozzie Bear Cannot post "Sneaky URL"!! Using Outlook.com accounts in Outlook 2
Commodore Search cannot complete the indexing of your Outlook data Using Outlook 6
S Outlook 2010 Cannot Open Attachments Using Outlook 14
Brian Murphy Exchange Online Everything a Transport Rule should do and cannot Exchange Server Administration 1
A Error: The name cannot be matched to a name in the address list Using Outlook.com accounts in Outlook 0
K Other Shared Calendar on Outlook cannot change items Using Outlook.com accounts in Outlook 7
T Outlook 2016 cannot connect after transition to Outlook Mail Using Outlook.com accounts in Outlook 4
L Cannot Connect to BCM database BCM (Business Contact Manager) 9
A Cannot connect to BCM database BCM (Business Contact Manager) 3
Diane Poremsky The custom form cannot be opened. Outlook will use an Outlook form instead. New Slipstick.com Articles 0
T Cannot access outlook.com accounts in Outlook 2016 Using Outlook 1
T Outlook 2016 mp3 attach "this file cannot be preview because there is no previewer installed for it" Using Outlook 1
S Outlook data file cannot be accessed message Using Outlook 5
O Cannot open or save calendar items Using Outlook 0
J Can Click to Drag Custom Form Field But Cannot Drop When Designing in Outlook 2007 Outlook VBA and Custom Forms 2
Diane Poremsky Cannot start Microsoft Office Outlook Error Message New Slipstick.com Articles 0
wallisellener "The database name cannot exceed 50 characters" BCM (Business Contact Manager) 2
J Your IMAP server wants to alert you to the following: cannot remove system folder Using Outlook 3
G Calendar folder cannot be found (tasks folder cannot be found) Using Outlook 2
K Cannot consistently drag and drop .msg files into Outlook 2010 Using Outlook 0
V Cannot start Outlook. Cannot open the Outlook window. Using Outlook 8

Similar threads

Top