convert an Outlook macro to Excel macro

[smiler44]

I am trying to move emails from an inbox to different sub folders and count up the number of unread emails in the inbox and sub folders.


I need to check the subject line for some information such as abcd123456.
if this is found I need to go off and do something. I have the excel code to go and do something.

Once I have done something I should be able to work out which sub folder to put the email into. I then need to send the email to this folder.

Any email that shows as being read I need to change to unread.

I have this excellent code below provided my a member of a forum for Outlook but it wont work in Excel, only Outlook.
Can you please convert it to VAB code that excel will understand? I have tried and failed

Excel does not understand:
Sub pattern_abcd123456(MyMail As MailItem, fndFlag)
or
Dim NS As Outlook.Namespace

both give user defined type not defined, I have no idea what to check if anything in Tools, References

 sub moveemail()
 ' In the Visual Basic Editor (VBE)
'  Tools menu | References...
'  Tick the entry for
'   Microsoft VBScript Regular Expressions 5.5
 Dim NS As Outlook.Namespace
Dim searchFolder As folder
Dim searchItems As Items
Dim moveToFolder As folder 'Outlook.MAPIFolder
Dim msg As MailItem
Dim foundFlag As Boolean
Dim i As Long
 Set NS = Application.GetNamespace("MAPI")
 On Error Resume Next    ' To bypass the error when the source folder is not found.
                        ' searchFolder will be Nothing
 ' Enter the exact names of the folders
' No slashes. Walk the path one folder at a time.
Set searchFolder = NS.Folders("Personal Folders").Folders("inbox").Folders("testin").Folders("testout")
Set moveToFolder = NS.Folders("Personal Folders").Folders("Drafts").Folders("testing").Folders("test")
On Error GoTo 0
 If searchFolder Is Nothing Then
   MsgBox "Source folder not found!", vbOKOnly + vbExclamation, "searchSubject error"
   GoTo ExitRoutine
Else
   Debug.Print vbCr & "searchFolder: " & searchFolder
End If
 Set searchItems = searchFolder.Items
 For i = searchItems.Count To 1 Step -1
    If searchItems(i).Class = olMail Then
        Set msg = searchItems(i)
        pattern_abcd123456 msg, foundFlag
         If foundFlag = True Then
            Debug.Print " Move this mail: " & searchItems(i)
            MsgBox (searchItems(i))
            'objItem.Move moveToFolder
            searchItems(i).Move moveToFolder
        End If
    End If
Next
 ExitRoutine:
     Set msg = Nothing
    Set searchItems = Nothing
    Set searchFolder = Nothing
    Set NS = Nothing
MsgBox ("all mail items checked")
End Sub
 
Sub pattern_abcd123456(MyMail As MailItem, fndFlag)
     Dim subj As String
    Dim re As Object
    Dim match As Variant
     fndFlag = False
    subj = MyMail.Subject
     Set re = CreateObject("vbscript.regexp")
    re.Pattern = "[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]"
     For Each match In re.Execute(subj)
        fndFlag = True
        Debug.Print vbCr & subj
        Debug.Print " *** Pattern found: " & match
    Next
 End Sub

thank you in advance

smiler44

 

[BigHappyDaddy]

In Tools Reference, look for the file named "Microsoft Outlook XX.X Object Library". That should get you past your current issue.

 

[Rx_]

This is a graphical example. Then use the instructions in the answer above.
http://www.cpearson.com/excel/MissingReferences.aspx

 

[smiler44]

BigHappyDaddy, thank you very much. It has indeed overcome the user defined problem.

Now it does not like :
Set NS = Application.GetNamespace("MAPI")

this gives Run time error 438 object does not support this property or method
so I changed it to this, but does it do the same thing?

Set NS = CreateObject("Outlook.Application").GetNamespace("MAPI")


Using F8 I seem to have been able to go through each line of code, goodness this is mentally tiring

smiler44

 

[Rx_]

LOL
MAPI needs a Reference set - same process.
With out a Reference to the MAPI object, the compiler doesn't recgonize any property, method or events.

 

[smiler44]

fantastic, with your help this now works in it's test state and I can see no reason for it not to work when used in anger.
I'm chuffed but it's not code I have written and truth be known, I don't understand it.

that you RX_ and BigHappyDaddy for your help.

Any more questions I do a new thread

smiler44