-
VBA in Outlook
Hi,
I need a macro / VBA to read my outlook emails and perform a task for me.
Here is what I need.
XYZ Mailbox - This mailbox will get 7 emails from the same sender and with the same subject line. I want my Macro to understand that there are 7 emails which have the same subject line move them to a folder and send one email out as acknowledgement.
Can someone help?
Thanks
-
- Do the emails arrive at the same time or day or in a week ?
- To count for 7 emails ... Is the maximum always 7 . And when do we have to stop counting the mails.
- I would probably try something with a getsetting and savesetting thing with the keywords :
1. emailadress
2. subjectline
3. emailno
3. holds the no of emails that comes in with the same settings.
(Or an excel file with every row a different mail... If column a - address, column b - subject, are the same as the new mail, column c is +1 ... and we skip this until column a is blank. If blank, new line for a new mail to check ...)
- Check with getsettings if the settings exists on every new mail starting from the time that the macro is implemented by using a rule.
- add 1 to emailno-keyword if it's not 6
- if email-no keyword = 6 then every mail in inbox that has the same adress and subject send to certain folder and create a new message to the adress of last email
This is only a rough sketch of my thoughts. Most likely it needs some finetuning before you could use this.
- You will probably also need a utility to bypass the security warning that outlook will give when you want to process the mails and send a new mail.
-
500587890
You probably need this utility that Charlize was talking about (at least this is just one of them)
http://www.contextmagic.com/express-clickyes/
Hope this helps
Seamus
-
As a warm-up, you could try this (paste everything in a module in outlook and create a rule for every mail that comes in. When placed on top, it will execute before every other rule.). For the moment it will log every mail that comes in and writes the sendername and subject to an excel file. You need to install that express clickyes utility to click away the security warnings that outlook will give.
Attached you'll find the excel file I used for this purpose. Save it in c:\data\mail_log under the name mail_log.xls . I hope this will give you a boost in the right direction.[vba]Option Explicit
' Declare Windows' API functions
' To be used with ExpressClick Yes
Private Declare Function RegisterWindowMessage _
Lib "user32" Alias "RegisterWindowMessageA" _
(ByVal lpString As String) As Long
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As Any, _
ByVal lpWindowName As Any) As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long
'These are used by the utility ClickYes Freeware
Public wnd As Long
Public uClickYes As Long
Public Res As Long
Sub Mail_log(myItem As Outlook.MailItem)
Dim i As Long
Dim oExcel As Object
Dim oWb As Object
Dim oWs As Object
Set oExcel = Application.CreateObject("Excel.Application")
oExcel.Visible = False
'C:\Data\Mail_log\Mail_log.xls is the file where we log
'all the mails that come in
Set oWb = oExcel.Workbooks.Open("C:\Data\Mail_log\Mail_log.xls")
Set oWs = oWb.Worksheets(1)
i = 2
Do While oWs.Range("A" & i).Value <> vbNullString
i = i + 1
Loop
Call PrepareClickYes
oWs.Range("A" & i) = myItem.SenderName
Call PerformClickYes
oWs.Range("B" & i).Value = myItem.Subject
oWb.Save
oWb.Close
Set oWs = Nothing
Set oWb = Nothing
Set oExcel = Nothing
End Sub
Sub PrepareClickYes()
'called before attempting to manipulate a message
uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")
wnd = FindWindow("EXCLICKYES_WND", 0&)
Res = SendMessage(wnd, uClickYes, 1, 0)
End Sub
Sub PerformClickYes()
'called directly after the code that manipulates
'a message. Clicks the yes and places the ClickYes utility
'back in suspend mode. When some other routine (that's not
'controlled by you) wants to do something with your
'messages, you still get that warning.
Res = SendMessage(wnd, uClickYes, 0, 0)
End Sub[/vba]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules