CODEWITHSUNDEEP
vbaemailexceloutlook
redd
redd

Reputation: 145

Changing the category tag of an email according to data in excel

So I basically want to loop through each email in the Inbox until the subject line in column A matches the subject line of the email. When it has found a match, it will then look along the same row in column C and depending on what is found here (YES, NO or MULTIPLE) the category of the email will change.

Every mail is tageed as "ATLG" before the macro has run.

The code below is very much a work in progress, but I do not know where to go from here. Any suggestions?

Dim mail As Outlook.MailItem
Dim fld As Outlook.MAPIFolder

For Each mail In fld.Items
With mail
If mail.Subject = Sheets("Mail Report").Range("A65000").End(xlUp).Value Then
If Sheets("Mail Report").Range("C65000").End(xlUp).Value = "YES" Then
mail.Category = "ATLG"
If Sheets("Mail Report").Range("C65000").End(xlUp).Value = "NO" Then
mail.Category = "MAN"
If Sheets("Mail Report").Range("C65000").End(xlUp).Value = "MULTIPLE" Then
mail.Category = "MULT"
Next mail

Excel Info

Upvotes: 0

Views: 733

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Is this what you are trying? (Untested)

Dim mail As Outlook.MailItem
Dim fld As Outlook.MAPIFolder
Dim ws As Worksheet


Set ws = Sheets("Mail Report")

Set fld = GetObject("", "Outlook.Application") _
.GetNamespace("MAPI").Session.PickFolder
If fld Is Nothing Then
   MsgBox "No Inbox selected"
   Exit Sub
End If

'~~> Find last row in Col A
lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For Each mail In fld.Items
    With mail
        '~~> Loop through values of Col A
        For i = 2 To lRow
            '~~> Match subject
            If mail.Subject = ws.Range("A" & i).Value Then
                Select Case UCase(ws.Range("B" & i).Value)
                    Case "YES": mail.Categories = "ATLG"
                    mail.Save
                    Case "NO": mail.Categories = "MAN"
                    mail.Save
                    Case "MULTIPLE": mail.Categories = "MULT"
                    mail.Save
                End Select

                Exit For
            End If
        Next i
    End With
Next mail

I finally got your code above to work. It was simple as using mail.Save below each Case line.

Upvotes: 1

Related Questions