[RESOLVED] [ASK] Outlook VBA confirmation auto reply while receive specific format subject

[meng-q]

Dear all masters,

Hi, im a newbie in in programming. I found that there are a lot of masters of programming in here, and i really need some help.

I've searched around and didnt find how to solve my problem. I've searched for the auto reply VBA function in outlook but still didnt find the logic about what i wanna do in outlook. I want to make my outlook email like an auto reply confirmation message.

I've a lot of deviation memo from many users, and some of them always ask me whether our division have already proceed their memo or not. What we usually do is, copy the ID Memo, and look around it in the Excel, and then reply to them the result is DONE/still in progress. And my question is, is it possible to make those things worked automatically? Lets say that they will email us with the specific subject like "Confirmation_memo 1234", and the outlook will automatically find the memo ID 1234 (in excel or somewhere which is convenient for us to updated the data) and it will auto-replied the result is to the sender.

Oh yeah and i hope i still can work with that computer too while it will autoreply the confirmation message.

Last words, i would like to say thank you. Really hope to hear from you soon.
Thanks!

[si_the_geek]

Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)

[meng-q]

thanks to moderator for moved my thread. sorry i didnt know that i should post it to the office development

hope i can get some help here

thanks

[westconn1]

And my question is, is it possible to make those things worked automatically?

yes that can work

Oh yeah and i hope i still can work with that computer too while it will autoreply the confirmation message.

that should not be a problem unless the number of emails is huge

you need to code in the application.newmail event, check the subject, look up details as required, create and send reply

if the details are in excel you will need to automate excel to find the details, check out the tutorial in the FAQ thread at the top of this forum, on automating office applications

[meng-q]

my problem is , i dont know how to automate the excel to find the details, works in the background.
OK i'll try to find the tutorial. hope i can find it
thank you

[westconn1]

obviously no one can help you from the details provided so far

[meng-q]

After trying to combine some function that i've found, here's the result. If you have any easier way or if you have advice, pls feel free to correct me


Public WithEvents myOlItems As Outlook.Items

Public Sub Application_Startup()

Set myOlItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("CONFIRMATION").Items

End Sub


Private Sub myOlItems_ItemAdd(ByVal Item As Object)
Dim MAPISession As Outlook.NameSpace
Dim MAPIFolder As Outlook.MAPIFolder
Dim MAPIMailItem As Outlook.MailItem
Dim oRecipient As Outlook.Recipient

If TypeName(Item) = "MailItem" Then
If Left(Item.Subject, 4) = "CONF" Then

Set MAPISession = Application.Session
MAPISession.Logon , , True, False
Set MAPIFolder = MAPISession.GetDefaultFolder(olFolderOutbox)
Set MAPIMailItem = MAPIFolder.Items.Add(olMailItem)

MAPIMailItem.Subject = "RE : Confirmation memo no " & Right(Item.Subject, 4)

Set oRecipient = MAPIMailItem.Recipients.Add(Item.SenderEmailAddress)
oRecipient.Type = olTo
Set oRecipient = Nothing

MAPIMailItem.Body = CHECKING(Right(Item.Subject, 4))
MAPIMailItem.Send
MAPISession.Logoff
End If
End If
End Sub


'while i put vlookup function to the excel to check whether the memo already proceed or not

Public Function CHECKING(nomemo As String) As String
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")

appExcel.Application.DisplayAlerts = False
appExcel.Workbooks.Open ("d:\testing.xls")

Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
'appExcel.Application.Visible = True
wks.Range("A1").Value = nomemo
wks.Calculate
CHECKING = wks.Range("B1").Value
wkb.Close

End Function