Solved

I need macro to scan all calendar events and pull out the email addys

Posted on 2010-11-23
12
209 Views
Last Modified: 2013-02-06
I need macro to scan all calendar events and pull out the email addys
0
Comment
Question by:Caiapfas
  • 5
  • 4
12 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 34202381
Hi, Caiapfas.

Pull out what addresses?  The organizer's address for meetings?  The addresses of other meeting attendees?  Both?  What do you want to do with the addresses?
0
 
LVL 2

Author Comment

by:Caiapfas
ID: 34202461
good question, i need the macro to pull out of content of the appt.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34202548
Sorry, I don't understand.  Can you clarify?  Also, what do you want to do with whatever is pulled out?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Comment

by:Caiapfas
ID: 34202975
the part of the appt that you could type a book.
i have email addys in there

dump them to a cvs file
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34204325
Oh, so you want to pull email addresses from the notes section of the appointment.  That's different and a bit more difficult.  I'll put something together and post it as soon as I can, hopefully by the end of the day.
0
 
LVL 2

Author Comment

by:Caiapfas
ID: 34206553
you rock, thanks for all the help!
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 34448213
Clearly that took me a lot longer than I had said.  I apologize for that.

Here's the code.  Follow these instructions to add it to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

To use the code

1.  Run the macro GetAddressesFromAppointments
Sub GetAddressesFromAppointments()
    'On the next line edit the path and name of the file'
    Const FILE_NAME = "C:\eeTesting\Addresses.csv"
    Dim olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, strAddresses As String, objFSO As Object, objFile As Object
    Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items
    With olkItems
        .Sort "[Start]"
        .IncludeRecurrences = True
    End With
    For Each olkAppt In olkItems
        If (Year(olkAppt.Start) <= Year(Date)) And (Month(olkAppt.Start) <= Month(Date)) And (Day(olkAppt.Start) <= Day(Date)) Then
            strAddresses = strAddresses & GetEmailAddresses(olkAppt.Body)
        End If
        DoEvents
    Next
    Set olkItems = Nothing
    Set olkAppt = Nothing
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(FILE_NAME)
    objFile.WriteLine Mid(strAddresses, 1, Len(strAddresses) - 1)
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    msgbox "Done"
End Sub

Function GetEmailAddresses(strBody As String) As String
    Dim objRegEx As Object, colMatches As Object, varMatch As Variant
    Set objRegEx = New RegExp
    With objRegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = "([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})"
        Set colMatches = .Execute(strBody)
    End With
    For Each varMatch In colMatches
        GetEmailAddresses = GetEmailAddresses & varMatch & ","
    Next
    Set objRegEx = Nothing
    Set colMatches = Nothing
End Function

Open in new window

0
 
LVL 2

Author Comment

by:Caiapfas
ID: 35985008
will give this a try
0
 
LVL 17

Expert Comment

by:Karen Falandays
ID: 38860315
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 38860316
I object.  The code I posted does exactly what the OP asked for.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question