Is this possible to do with a macro?

[joe1250]

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$
.Show
If .SelectedItems.Count <> 0 Then
xDirectFldrRoot$ = .SelectedItems(1) & "\"
Else

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\\

Next


End Sub

Any assistance or advice would be very much appreciated

Thanks,
Joe

 

[Diane Poremsky]

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).

 

[joe1250]

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

Thanks,
Joe

 

[Diane Poremsky]

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

or
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

 

[Diane Poremsky]

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

 

[joe1250]

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!
Joe

 

[joe1250]

figured out this missing Case 3 value... capitalized N in number should have been lower case :0

 

[Diane Poremsky]

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

 

[joe1250]

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!

Thanks,
Joe

 

[joe1250]

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)
Save

next i

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

Thanks,
joe

 

[Diane Poremsky]

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...

 

[joe1250]

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

 

[joe1250]

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)...

 

[joe1250]

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...

Thanks,
joe

 

[Diane Poremsky]

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.

 

[joe1250]

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!

Thanks,
joe

 

[joe1250]

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?

Thanks,
joe

 

[joe1250]

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.

Thanks,
Joe