Is this possible to do with a macro?

Not open for further replies.


Outlook version
Email Account
Exchange Server 2007
I am "familiar" with creating (and recording) macro's in Excel, but don't really know a lot about how to get the most out of Outlook (2007). This time of year, we get a lot of emails generated from a webform on our website and we are then required to create folders on our server for each email and save the email to. I was hoping there might be some way to automate at least some of this process, but Outlook is really unfamiliar to me and without a recorder function to at least help reverse engineer certain actions... I'm just not sure where to start.

I made a macro (with help from the excel forums) to create folders based on values in an excel spreadsheet, which is similar in concept to what I'd like to do in outlook. In this macro, each cell value looked something like "22-265-02349856". The macro would then prompt the user for the root folder location and then loop through all the cells in the range and create the folder structure with folder levels Folder level 1: "22" -> subfolder "265" -> subfolder "02349856"

I'm hoping to be able to do something similar in outlook. I think the best possible option would be to select the values (from 3 cells) from a table in the message body (the result of the webform submission), which form the basis for the folder path I'd like to create. So like the excel example above, the table in the body of my outlook message would have 3 cells, each containing one of the values 22, 265, and 02349856. Is there a way to select these 3 cells and then run a macro via hotkey that would create the folders and subfolders (unless they already exist) for each of 22 -> 265 -> 02349856 and then save a copy of the message in the last folder 02349856?

This is my excel macro, I feel like if I just knew how to reference the table from the message body, I could probably figure out at least a viable attempt at how to modify it... assuming of course MkDir works as well in Outlook as it does in Excel... Basically I would like to remove the loop and replace the
FileRef = ActiveSheet.Range("A" & i).Text
with something that acts like FileRef = "Selected cells from table where
FldrLvl1 = "First or top Cell"
    FldrLvl2 = "Second or Middle Cell"
    FldrLvl3 = "Third or Bottom Cell"

Does that make sense? Here's the Excel code...

Option Explicit

Sub CreateFileFolders()

Dim LastRow As Long, i As Long, n As Long
Dim FldrRoot As String, FldrLvl1 As String, FldrLvl2 As String, FldrLvl3 As String, FileRef As String
Dim xDirectFldrRoot$, InitialFoldr$

InitialFoldr$ = "H:\"
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select the parent folder; subfolders will be created. "
.InitialFileName = InitialFoldr$
If .SelectedItems.Count <> 0 Then
xDirectFldrRoot$ = .SelectedItems(1) & "\"

End If
End With

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
FldrRoot = xDirectFldrRoot$

If Cells(1, 1).Value Like "*201*" Then i = 1 Else: i = 2

For i = i To LastRow

    FileRef = ActiveSheet.Range("A" & i).Text   '//FileRef is ##-###-########...\\

    FldrLvl1 = Left(FileRef, 2) '//Extract first 2 characters from sting\\
    FldrLvl2 = Mid(FileRef, 4, 3) '//Extract middle 3\\
    FldrLvl3 = Right(FileRef, 8) '//Extract last 8\\
    On Error Resume Next '//if folder exists - igonore error and carry on\\
    MkDir (FldrRoot & "\" & FldrLvl1) '//Create Level 1 folder (eg; 22)\\
    MkDir (FldrRoot & "\" & FldrLvl1 & "\" & FldrLvl2) '//Create Level 2 folder (eg; 265)\\
    MkDir (FldrRoot & "\" & FldrLvl1 & "\" & FldrLvl2 & "\" & FldrLvl3) '//Create Level 3 folder (eg; 02349856)\\
    On Error GoTo 0 '//resume errors\\


End Sub

Any assistance or advice would be very much appreciated :)

If the values you need from outlook can be identified, using either regex or mid/instr functions, you should be able to do this. Some people have problems saving to the network location but that would be the only other possible issue - getting the code from the message will be the main issue.

See Use RegEx to extract text from an Outlook email message for the regex method. If the numbers are in the body in 01 234 5467890 format when you convert it to plain text, it would be easy to pick up (and you can convert it temporarily, just to grab the code).
Thanks Diane! so this is a screenshot of the part of the table in the body of the message that contains the relevant info I need to create the folder structure... the whole table is larger with other details, but this all I really care about at this point.


So when you say
when you convert it to plain text
do mean the whole message? or just the table? or just the part of the table above? To further complicate things there may be more than one table containing different details in the message, which would each require separate folders... I saw in some examples on that link you posted that it is possible to pull out multiple occurrences of the pattern so this would be an option, but was also curious if there was some sort of "from selection" code I could use so that the user would only need to select the 3 cells from the table above containing the values 22, 205, and 00323532 hit the hot key assigned to the macro to create the folders and save a copy of the message... I feel like this would be the preferred method for the my co-workers, who will be using anything I am able to create for them :)

As long as the data is identified, it's not a problem. Getting it from tables can be tricky, but if that fails, plain text format will work. The macro would convert it and get the data them close it without saving, so the format is not destroyed.

in plain text, it will either be
Area: 22
Jurisdiction: 205


we can definitely pick up the values.

Using the get 2 or more values sample, you'd use a case statement to get the values.

Select Case i
Case 1
.Pattern = "(Area[:]\s*([\d]*))"
.Global = False

Case 2
.Pattern = "(Jurisdiction[:]\s*([\d]*)"
.Global = False

Case 3
.Pattern = "(Roll Number[:]\s*(\d*))"
.Global = False
End Select
run this macro on one of the messages - how does the table look? (In a quickie test, it should pick up the table up in a usable format.)

Sub TestMsg()
    Dim olMail As Outlook.MailItem
    Dim strBody As String
    Dim testSubject As String
    Set olMail = Application.ActiveExplorer().Selection(1)
    strBody = olMail.Body
    MsgBox strBody
End Sub
Thanks so much for all your advice so far Diane, I really appreciate it :)

For some reason that last sub doesn't apply to the whole message (unless it does, but I can only see the top section that fits in the message box? can't seem to scroll or resize the message box to see if it continues beyond the visible window). There is a small empty table at the beginning of the message followed by some general text comments (boilerplate) before the relevant table, and this is what the code displays in the MsgBox. I could pm or attach an example of the message format if that would be helpful, please just let me know the best method for doing so.

I have also been playing around with the example code from your previous reply and the 2 or more values example and I'm not sure the best way to modify it... Based on an example where Case 1 returns 22; Case 2 returns 205; and Case 3 returns 00111111, the end result I'm looking for is to be able to store the Case 1 value as a variable (FldrLvl1) and the Case 2 and 3 values as another variable (FldrLvl2 formatted as Case2-Case3 ie: 205-00111111)... So do I string them all together and then use left and right functions to assign values to the variables or do modify the loop somehow so the variables won't be overwritten every time it runs? Currently I'm getting the correct values returned for Case 1 and 2, but I must have something wrong with my pattern definition because Case 3 is returning nothing.
   Case 3
    .Pattern = "(Roll Number[:]\s*([\d]*))"
    .Global = False

Finally, I will be needing to figure out a larger loop as there may be multiple sets to be matched in the message. What I mean by this is there maybe several "sets" (or Reg's? ie Reg1, Reg2, Reg3?) of three Cases to be extracted... if that makes sense? :)

Thanks again!
figured out this missing Case 3 value... capitalized N in number should have been lower case :0
As long as the case never changes (and in your case, it shouldn't change), that will work... other options are to change the body to lower case and use all lower case in the case statements or tell regex to ignore case by adding .IgnoreCase = True under .global
Thanks Diane, so I'm a bit stuck at the moment trying to figure out how to get the code to loop through to look for other instances of matches for the patterns in the email. Right now it finds the first set of 3 matching patterns and correctly identifies the values, but then exits the loop and completes the sub. What I would like is after the first set of matches is complete, to store that string as a variable for use later and then search for the next set of 3 patterns in the body of the email. So for example if the message contained a table like this...


Currently I'm only getting the first set... would I want to do a preliminary count for something (a key word or # of matching sets?) and then use the returned value from the count with a loop (For i = 1 To Countvalue) and start this loop before the
Set Reg1 = New RegExp
? Would Reg1 then become Regi? (or does Reg not work like this because it is an object and not a variable?)

Based on the example table above, I am looking to end up with the following variables:

Countvalue = 2
SetValues = "IN 2220500111111,2220500111112"...(So this would basically be my equivalent to ("IN" & testSubject1,testSubject2) in the example code.

In order to get both of these matches in this format I would have to run the loop through all instances which would mess up the following variables and process as it would be best if I could get this string before doing the saves... maybe this variable will have to be foregone?)

These next variables would be overwritten during subsequent runs through the loop so the first one would be...
FldrLvl1 = 22
FldrLvl2 = 205-00111111
(the macro would create the folders at this point and save a copy of the message in FldrLvl2 after prompting the use for the rootfolder location)

The second time through the loop these variables would be...
FldrLvl1 = 22
FldrLvl2 = 205-00111112
(the macro would create the folders at this point and save a copy of the message in FldrLvl2 after prompting the use for the rootfolder location)

I've never had a really clear understanding of objects at a fundamental level and how they operate and I'm not familiar with Reg so I'm struggling a bit more with this than I expected... This part in particular is a bit of a mystery as to what the pieces are doing and how/why...

If Reg1.test(olMail.Body) Then
        Set M1 = Reg1.Execute(olMail.Body)
        For Each M In M1
            Debug.Print M.SubMatches(1)
            strSubject = M.SubMatches(1)

Any enlightenment you could provide would be greatly appreciated!

Just occurred to me that as long as I was able to save the testSubject(1,2,3...i) variables, I could use them at any point to create the FldrLvl variables...
ie... or something like this... just as a concept, I know the syntax is probably wrong, I'll have to go figure it out...
FldrLvl1 = Left(testSubject1,2)
FldrLvl2 = FldrLvl1 & "-" & Right(testSubject1,11)

next i

FldrLvl1 = Left(testSubject2,2)
FldrLvl2 = FldrLvl1 & "-" & Right(testSubject2,11)

you can use more Case statements - oh, you can't easily because they use the same name. You could use global = true and capture both.
i = 1 to 6

Case 4
.Pattern = "(Area[:]\s*([\d]*))"
.Global = False

Case 5

you can assign each case to a different variable - this would be where you need to use left and right functions (or split)
if i = 1 then strArea1 = strSubject, strArea1 (this should create a tab separated list)
if i = 2 then...
ok, so I would create as many Cases to suite the most likely highest number of possible occurrences?

Seems like there be some way to count... so if a "match set" = cases 1 through 3, and the macro counted there were 3 "match sets" in the message, then I could tell it to run the loop 3 times, but start i over at 1 for each loop... or something like that....For i = 1 To MatchSetCount * 3 (equivalent to For i = 1 To 9)

    With Reg1
        Select Case i

(I know this doesn't work, but feels like there should be an elegant way to either leave this at 1 and run the loop 3 times or use the i variable with a mathematical function like +1 or something to increment it along with the progression...)
     Case i
    .Pattern = "(Area[:]\s*([\d]*))"
    .Global = True
    Case i + 1
    .Pattern = "(Jurisdiction[:]\s*([\d]*))"
    .Global = True
    Case i  + 2
    .Pattern = "(Roll number[:]\s*((\w)*))"
    .Global = False
Interesting... when i create the 6 case statements and set .Global = True, I do get all of the values, but not in the right sequence... so rather than (Case 1,2,3), (Case 1,2,3) I get (Case 1,1), (Case 2,2), (Case 3,3)...
would it make sense to use an array? maybe something like
Dim arr
arr = Array("Case 1","Case 2","Case 3")

I'm not sure exactly how to incorporate this, but seemed like maybe it would treat them as groups of pattern matches rather than individual pattern matches? Then maybe could do a count... MatchCount = M1.count?

Then use this for the loop? For i = 1 To MatchCount

I'm also not having much luck getting the if statements you suggested working... I know it' something simple that I'm missing, been a while since I did much with VBA, but keep getting hung up on the Next statement without For, but I can't see why...

if i = 1 then strArea1 = strSubject, strArea1 (this should create a tab separated list)
if i = 2 then...

Interesting... when i create the 6 case statements and set .Global = True, I do get all of the values, but not in the right sequence... so rather than (Case 1,2,3), (Case 1,2,3) I get (Case 1,1), (Case 2,2), (Case 3,3)...
Right... because 2 values match each case (so you only need 3). You will probably need to split it as an array - i'll try to test it and the If statements today.
Thanks Diane, I spent the bulk of the day yesterday (and will do so today as well) working on this and trying out different things. While I'm no closer to making something that does what I need, I have learned a lot about the Regex object and how it works... very cool, I hope I am able to learn enough to use it on a regular basis without requiring as much assistance!

Hi Diane, I hope you had a wonderful holiday season! I'm still a bit stuck on how to use an array here and was hoping if you had time you might be able to push me in the right direction?

Hi Diane, I just wanted to let you know that I've also posted a question on Mr. Excel pertaining this thread and the topic of how I might tackle the array issue. I'm not sure how busy you are this time of year so I figured I'd ask around over there in case you're unavailable. Just wanted to let you know in case I should be linking something from this to that... not really sure how that works.

Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
P Is it possible to write a macro to email to all addresses of selected contacts? Using Outlook 1
D Frequently used response short cut possible macro Using Outlook 1
P Possible to write a macro to print all attachments with specific . Outlook VBA and Custom Forms 1
C New pc, new outlook, is it possible to import auto-complete emailaddress Using Outlook 4
e_a_g_l_e_p_i Is it possible to have a reminder in Outlook 2021 for every 90 days Using Outlook 3
W Outlook 2016 MSI - Possible to make work with O365 modern Auth & Win7? Using Outlook 4
O Outlook - How to embed font ? (If at all possible) Using Outlook 2
S Outlook 365 Can I change the possible range of highlighting colours when writing an Outlook email? Using Outlook 1
T Outlook 2013 Possible interference by Telstra ISP with Hotmail Password Using accounts in Outlook 4
e_a_g_l_e_p_i Is it possible to transfer things from one calendar to another Using Outlook 2
V Is it possible to collect statistics from Outlook forms? Outlook VBA and Custom Forms 1
e_a_g_l_e_p_i Is it possible it set the fonts used to read incoming mail Using Outlook 25
S Is it possible to cusstomise selectable Options in Kalander. Outlook VBA and Custom Forms 1
J Is it no longer possible to suppress Outlook 2019 Invalid Certificate name mismatch security alert via Registry? Using Outlook 1
BretAB Is it possible to add a lookup field to a Message form? Outlook VBA and Custom Forms 4
A Possible to hide ribbon with custom appointment form? Outlook VBA and Custom Forms 3
M possible to search Outlook for multiple email addresses at once? Using Outlook 1
semnaitik Is it possible to recover content from a 0KB PST file? Using Outlook 3
D Is it possible to automatically send an email when it is moved to a folder? Exchange Server Administration 1
T Exchange setup not possible with username Using accounts in Outlook 0
H Is it possible to create a view that only show the first task in a list? Using Outlook 1
H Is It Possible to See Who Has Viewed My Calendar in Exchange? Using Outlook 2
P Is it possible to convert address book to "Auto-Complete List" (NK2)? Using Outlook 5
C in outlook 2007-Contact Group-One name have 3 mail id-its possible? Using Outlook 3
J convert .pst to .eml, how is it possible? Using Outlook 1
S Is it possible to recover a permanently deleted email from my .OST file? Using Outlook 0
M Is it possible to restore deleted webmail to server? Using Outlook 3
D is it possible to have both a POP and IMAP account in Outlook 2013? Using Outlook 5
C Is it possible to have contacts sync real-time between Outlook and iCloud? Using Outlook 1
P Is it possible to sort emails by COUNT of "From" field? Using Outlook 2
Justo Horrillo It's possible to modify the behaviour of the conversation option? Using Outlook 2
S Possible to link to ics file without importing? Using Outlook 4
P Outlook 2003 - possible to recreate corrupt account? Using Outlook 3
FirefIy Marking message read only if replied to the message or Ctrl+Q. Possible? Using Outlook 1
O Script to move emails to a folder based on various possible keywords Outlook VBA and Custom Forms 11
A Is it possible to remove the word Categories: from task list views? Using Outlook 2
B Is it possible with Outlook form Outlook VBA and Custom Forms 5
Steve Jacobs Possible to configure TNEF options based on *sender*? Exchange Server Administration 3
S Is it possible to backup 2013 Email & Calendar info? Using accounts in Outlook 3
O Is it possible to go from BCM Office 13 back to BCM Office 2010? BCM (Business Contact Manager) 1
E Show field from account on contact form - possible? BCM (Business Contact Manager) 1
A Possible to modify people preview window? Using Outlook 1
T OL BCM 2007 - Possible to migrate SQL Server Express 2012 ? BCM (Business Contact Manager) 2
V Name of attachment in replies (HTML) - possible?? Using Outlook 12
C Is it possible: List of URL fields BCM (Business Contact Manager) 0
mrje1 Assigned Categories keep getting deleted in mail, bug? How to fix if possible? Using Outlook 5
Commodore Exporting all contacts to VCF files possible? Using Outlook 4
M I'd like to alter the appointment balloons. Possible? Using Outlook 6
J Is it possible to highlight a folder in Outlook 2007? Using Outlook 2
L I want ONE calendar if possible with no overlaying Using Outlook 1

Similar threads