Creating Macro to scrape emails from calendar invite body

Post number 5 has been selected as the best answer.

Status
Not open for further replies.
G

grabrail

Member
Outlook version
Outlook 365 64 bit
Email Account
Office 365 Exchange
Hi, first time poster.

We send calendar invites to various people throughout the month, in the body of the calendar we use a template, which is effectively just a table with various fields in, one of these fields will contain a number of different email address.

each month our admin girl has to go through these calendar entries, which there are lots of, and get the email addresses from the calendar bodies and send these emails a personalised reminder email, using a template from our CRM software.

What I am trying to do is, look to create a Macro that will scrape the body of any entries on the day you click the button, and take those email addresses, to send an email to each one, using a predefined email template.

I don't have a clue where to start, can anybody help.
 
Are they meeting requestsor was the appointment sent as an attachment to an email? ? A macro can pull the address out of the address fields.

You can use regex to find the addresses - Use RegEx to extract text from an Outlook email message -

This is from a macro that creates contacts from address in a word document - after you get the address, you get the next and add it to a string - then when finished, it can be written to the clipboard or a text file, new message body etc
Code: 
Dim Reg1 As RegExp
    Dim M1 As MatchCollection
    Dim M As Match
              
    Set Reg1 = New RegExp
    With Reg1
        .Pattern = "(([\w-\.]*\@[\w-\.]*)\s*)"
        .IgnoreCase = True
    End With
    If Reg1.test(ActiveDocument.Content) Then
    
        Set M1 = Reg1.Execute(ActiveDocument.Content)
        For Each M In M1
           strEmail = M.SubMatches(1)

strGroup = strGroup & strEmail & ";"
        Next
    End If
 
Hi, thanks for your reply. These are just normal Outlook claendar events. That are created by the user that needs to send the reminders
 
Code: 
Sub GetEmailAddressesAppt()
    Dim olAppt As Outlook.AppointmentItem
    Dim RegExp As Object
    Dim Reg1 As Object
    Dim M1 As MatchCollection
    Dim M As Match
        
    Set olAppt = Application.ActiveExplorer().Selection(1)
   ' Debug.Print olAppt.Body
                  
    Set RegExp = CreateObject("vbscript.regexp")
    
    Set Reg1 = New RegExp
    With Reg1
        .Pattern = "(([\w-\.]*\@[\w-\.]*)\s*)"
        .IgnoreCase = True
        .Global = True
    End With
    If Reg1.Test(olAppt.Body) Then
    
        Set M1 = Reg1.Execute(olAppt.Body)
        For Each M In M1
           strEmail = M.SubMatches(1)
          
           If strEmail = str1 Then GoTo nextM
          
        strGroup = strGroup & strEmail & "; "
        
     str1 = strEmail
nextM:
        Next
    End If
    
Set olObj = CreateObject("Outlook.Application")
Set oPost = olObj.CreateItem(olPostItem)
With oPost
    .Body = strGroup
    .Display
End With

End Sub

That code will add a list of addresses to a post form, removing duplicates (which occurs when the addresses are hyperlinked)
1701446267361.png
 
I cant thank you enough, this has given me a great starter to achieving what I need to do
 
Status
Not open for further replies.
< Applying new form to existing contacts -- MessageClass | Copying email address(es) in body of email and pasting in To field >
Similar threads
Thread starter Title Forum Replies Date
D Outlook 2016 Creating an outlook Macro to select and approve Outlook VBA and Custom Forms 0
N Help creating a VBA macro with conditional formatting to change the font color of all external emails to red Outlook VBA and Custom Forms 5
A Help creating macro for conditional formatting settings Using Outlook 8
F Creating Macro in Outlook 2003 Using Outlook 8
O Macro for creating hyperlinks in Outlook 2007 Using Outlook 3
I Creating an Outlook 2007 macro that attaches a signature to new me Outlook VBA and Custom Forms 1
T Outlook 2010 Creating a email 'Group' in OL 2010 Using Outlook 2
S Custom Contact card - need help creating one Outlook VBA and Custom Forms 1
G Event when creating task from mailitem Outlook VBA and Custom Forms 2
T Outlook creating unwanted tasks in Tasks and Todo from emails Using Outlook 1
Fozzie Bear Outlook 2016 Creating a shared local Contacts folder Using Outlook 2
R Creating a user defined function Outlook VBA and Custom Forms 3
M Creating an RSS Feed **FROM** Outlook 2013 Calendar. Using Outlook 5
O How to prevent CC from showing when creating a new mail? Using Outlook 1
N Creating a button or link to a form in the Organizational Forms Library Outlook VBA and Custom Forms 3
B Creating an email with the list of tasks Outlook VBA and Custom Forms 0
L Creating drafts when I thought I was sending Using Outlook 1
R Would creating a new profile cause Outlook to download all the old mails from the server? Using Outlook 1
A Creating Progress Bar or Status Bar Update Outlook VBA and Custom Forms 0
T Outlook creating a folder named: "Unwanted" Using Outlook 3
M Outlook 2007 Contacts Glitch: Creating a new email Using Outlook 1
Liza Creating a rule in outlook to filter messages Using Outlook 0
A Are categories still recommended for creating local distribution lists? Using Outlook 3
S Creating Email - Selecting Pre-Defined Text Using Outlook 2
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1
M Creating Outlook Appointments from Excel Cells Outlook VBA and Custom Forms 1
N Creating New Profile Using Outlook 0
Y Creating custom appointment request form with multiple mail recipients Outlook VBA and Custom Forms 5
M creating email from contact file = 3 emails in To field Using Outlook 3
P Recover / Extract Rules from standalone PST file creating RWZ file Using Outlook 2
A Creating an outlook rule to forward an email with a specific message Using Outlook 1
I Creating meeting invite with disabled tentative button Outlook VBA and Custom Forms 5
E Creating email templates for organizational use Using Outlook 0
N Creating or changing the main new mail message template in Outlook 2010 Using Outlook 2
D Creating custom view with VBA Outlook VBA and Custom Forms 2
J Outlook creating unwanted rule on its own Using Outlook 1
R Creating a Room Mailbox with Exchange Online Outlook VBA and Custom Forms 0
A Creating a rule on “Deleted items” folder Using Outlook 1
CMG73 Creating templates with predefined subject and CC Using Outlook 1
G Creating Contact Sub Folders Using Outlook 2
Rupert Dragwater creating gmail account in Outlook 2013 Using Outlook 7
nathandavies Creating a Select Case for a directory of folders Outlook VBA and Custom Forms 1
2 creating custom stationery Using Outlook 2
Fozzie Bear Creating Custom Meeting Form Outlook VBA and Custom Forms 6
U Creating a (This computer only) folder within an IMAP account directory Using Outlook 1
A Creating archive rule on the clients by script/ Outlook VBA and Custom Forms 3
J Creating a URL from a message body excerpt before forwarding Using Outlook 2
B Need Help Creating Email Based on Subject w Address in Body Outlook VBA and Custom Forms 1
A Creating rule to create week folder and transfer mail using alert Using Outlook 3
J Creating an "isolated" shared calendar in Outlook Outlook VBA and Custom Forms 1

Similar threads

Back
Top