Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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

I need macro to scan all calendar events and pull out the email addys
0
Caiapfas
Asked:
Caiapfas
  • 5
  • 4
1 Solution
 
David LeeCommented:
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
 
CaiapfasAuthor Commented:
good question, i need the macro to pull out of content of the appt.
0
 
David LeeCommented:
Sorry, I don't understand.  Can you clarify?  Also, what do you want to do with whatever is pulled out?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
CaiapfasAuthor Commented:
the part of the appt that you could type a book.
i have email addys in there

dump them to a cvs file
0
 
David LeeCommented:
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
 
CaiapfasAuthor Commented:
you rock, thanks for all the help!
0
 
David LeeCommented:
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
 
CaiapfasAuthor Commented:
will give this a try
0
 
Karen FalandaysTraining SpecialistCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
David LeeCommented:
I object.  The code I posted does exactly what the OP asked for.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now