[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Outlook 2007 - Macro syntax

I would like to modify the code that is in the code section.  The details are given before the procedure.  Thank u
The following code works.  What I would like to change is instead of 
the recip.type = olCC or recip.Type = olBCC
I would like the recip Type to - the Sender of the email.
What is the correct syntax to substitute for this;
and also where else the changes should take place.

Sub CCorBCC2()

Dim outputFile As Object
Dim objFSO As Object
Dim recip As Recipient
Const strFileSpec As String = "c:\Deleteme\CCorBCC.txt"

    If Application.ActiveExplorer.Selection.Count <> 1 Then Exit Sub
    MsgBox " I am inside the app "
    On Error Resume Next
    Set objFSO = CreateObject("scripting.filesystemobject")
    Set outputFile = objFSO.OpenTextFile(strFileSpec, 8)
    On Error GoTo 0
    If outputFile Is Nothing Then Set outputFile = objFSO.createtextfile(strFileSpec, True)
'    Set outputFile = objFSO.createtextfile(strFileSpec, True)
    For Each recip In Application.ActiveExplorer.Selection(1).Recipients
        If recip.Type = olCC Or recip.Type = olBCC Or recip.Type = olTo Then
            outputFile.Writeline recip.Address
        End If
    Next
    outputFile.Writeline "End of file" & " " & Now()
    outputFile.Close
    Set outputFile = Nothing
    MsgBox "end of processing "


End Sub

Open in new window

0
jegajothy
Asked:
jegajothy
  • 7
  • 6
1 Solution
 
Chris BottomleyCommented:
As below it's eaier since there is of course only the one sender address to deal with.

Chris
Sub SenderAddy()

Dim outputFile As Object
Dim objFSO As Object
Dim recip As Recipient
Const strFileSpec As String = "c:\Deleteme\CCorBCC.txt"

    If Application.ActiveExplorer.Selection.Count <> 1 Then Exit Sub
    MsgBox " I am inside the app "
    On Error Resume Next
    Set objFSO = CreateObject("scripting.filesystemobject")
    Set outputFile = objFSO.OpenTextFile(strFileSpec, 8)
    On Error GoTo 0
    If outputFile Is Nothing Then Set outputFile = objFSO.createtextfile(strFileSpec, True)
'    Set outputFile = objFSO.createtextfile(strFileSpec, True)
'    For Each recip In Application.ActiveExplorer.Selection(1).Recipients
'        If recip.Type = olCC Or recip.Type = olBCC Or recip.Type = olTo Then
            outputFile.Writeline Application.ActiveExplorer.Selection(1).SenderEmailAddress
'        End If
'    Next
    outputFile.Writeline "End of file" & " " & Now()
    outputFile.Close
    Set outputFile = Nothing
    MsgBox "end of processing "


End Sub

Open in new window

0
 
jegajothyAuthor Commented:
in response to chris, the macro is supposed to loop through all the messages in the Junk E-mail folder and copy the sender's email address.  It will be run with the Junk E-Mail folder selected.  
Sorry I did not sound clearer.   thank u
0
 
Chris BottomleyCommented:
Junk folder processed irrespective of actual folder selected
All items within the junk folder processed IF they are mails.

Chris
Sub SenderAddy()

Dim outputFile As Object
Dim objFSO As Object
dim mai as object
Const strFileSpec As String = "c:\Deleteme\CCorBCC.txt"

    MsgBox " I am inside the app "
    On Error Resume Next
    Set objFSO = CreateObject("scripting.filesystemobject")
    Set outputFile = objFSO.OpenTextFile(strFileSpec, 8)
    On Error GoTo 0
    If outputFile Is Nothing Then Set outputFile = objFSO.createtextfile(strFileSpec, True)
    For Each mai In application.Session.GetDefaultFolder(olFolderJunk).items
        IF mai.class = olmail then 
            outputFile.Writeline Application.ActiveExplorer.Selection(1).SenderEmailAddress
        end if
    Next
    outputFile.Writeline "End of file" & " " & Now()
    outputFile.Close
    Set outputFile = Nothing
    MsgBox "end of processing "

End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jegajothyAuthor Commented:
in response to Chris, thank u so much for your response.
However, the macro is not working correctly.
I have changed the name of the output file.
When it was run, here are the contents :
noreply@experts-exchange.com
noreply@experts-exchange.com
noreply@experts-exchange.com
End of file 10/15/2011 5:21:30 PM
user-1zz@imnicamail.com
user-1zz@imnicamail.com
user-1zz@imnicamail.com
End of file 10/15/2011 5:23:22 PM
The first time it was run it returned 3 names, and the 2nd time the same macro was run added the 2nd 3 set of names i,e. user-1zz@imnicamail.com.
But in the Junk E-mail folder there are 2,675 emails, most of them I have not even read it yet because judging from the sender's subject line, I can safely say it is a spam email.
I ran the same macro for the third time, and it added the following lines  although there were about 2675 emails in the Junk E-Mail folder:
user-1zz@imnicamail.com
user-1zz@imnicamail.com
user-1zz@imnicamail.com
End of file 10/15/2011 5:29:20 PM
So I am at a lost why it is not working as it should.  Hope u could throw some light.  Thank u once again for all your assistance.
Thank u
0
 
Chris BottomleyCommented:
Are you talking the outlook junk email folder or some other folder ... select the folder in outlook and then goto the vba editor and in the debug window, (ctrl + G to display) type:

?application.ActiveExplorer.CurrentFolder.FolderPath

What does it return?

Chris
0
 
jegajothyAuthor Commented:
In response to Chris,
it returned :
\\Jegajothy@msn.com\Junk E-mail
why are the next steps.  Again, a very big thank u for your response and time.
0
 
jegajothyAuthor Commented:
sorry the above should be : what are the next steps. ...."
and NOT "why are the next steps. ....."
0
 
Chris BottomleyCommented:
That looks like the folder I selected ... but to double check try:

?application.Session.GetDefaultFolder(olFolderJunk).folderpath
0
 
jegajothyAuthor Commented:

In response to Chris's
That looks like the folder I selected ... but to double check try:
?application.Session.GetDefaultFolder(olFolderJunk).folderpath

the result was :
\\Personal Folders\Junk E-mail

FYI, The Junk email folder is in : \\Jegajothy@msn.com\Junk E-mail

Thank u once again.
0
 
Chris BottomleyCommented:
Ah, that's different of course so ...
Sub SenderAddy()

Dim outputFile As Object
Dim objFSO As Object
dim mai as object
Const strFileSpec As String = "c:\Deleteme\CCorBCC.txt"

    MsgBox " I am inside the app "
    On Error Resume Next
    Set objFSO = CreateObject("scripting.filesystemobject")
    Set outputFile = objFSO.OpenTextFile(strFileSpec, 8)
    On Error GoTo 0
    If outputFile Is Nothing Then Set outputFile = objFSO.createtextfile(strFileSpec, True)
    For Each mai In application.Session.folders("Jegajothy@msn.com").folders("Junk E-mail").items
        IF mai.class = olmail then 
            outputFile.Writeline Application.ActiveExplorer.Selection(1).SenderEmailAddress
        end if
    Next
    outputFile.Writeline "End of file" & " " & Now()
    outputFile.Close
    Set outputFile = Nothing
    MsgBox "end of processing "

End Sub

Open in new window

0
 
jegajothyAuthor Commented:
In response to Chris, Thank u for your work.  
I selected the Junk E-mail folder and ran the macro.  Although this folder has more than 2,700 junk emails, but the code at :
    For Each mai In Application.Session.Folders("Jegajothy@msn.com").Folders("Junk E-mail").Items
        If mai.Class = olMail Then
            outputFile.Writeline Application.ActiveExplorer.Selection(1).SenderEmailAddress
        End If
    Next
When I stepped thru the code, the following:
     If mai.Class =olMail; only showed 43.  
Attached is the output file renamed as -2 for your inspection.
An inspection of the attached file showed that it has more than 2,700 entries, but all of the same Sender.  Looks like it did not loop thru the Folder.
Thank u.
NewJunkMailList2.txt
0
 
Chris BottomleyCommented:
OOPS!

Chris
Sub SenderAddy()

Dim outputFile As Object
Dim objFSO As Object
dim mai as object
Const strFileSpec As String = "c:\Deleteme\CCorBCC.txt"

    MsgBox " I am inside the app "
    On Error Resume Next
    Set objFSO = CreateObject("scripting.filesystemobject")
    Set outputFile = objFSO.OpenTextFile(strFileSpec, 8)
    On Error GoTo 0
    If outputFile Is Nothing Then Set outputFile = objFSO.createtextfile(strFileSpec, True)
    For Each mai In application.Session.folders("Jegajothy@msn.com").folders("Junk E-mail").items
        IF mai.class = olmail then 
            outputFile.Writeline mai.SenderEmailAddress
        end if
    Next
    outputFile.Writeline "End of file" & " " & Now()
    outputFile.Close
    Set outputFile = Nothing
    MsgBox "end of processing "

End Sub

Open in new window

0
 
jegajothyAuthor Commented:
I salute a genius for an awesome job very well done.  thank u.
0

Featured Post

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.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now