Outlook Macro: Send email when specific subject is received

[Richardxtc]

HI,

I have been trying to get help on this matter however the forum i was asking on was taking a while to respond. I need to get this code working, where an email with attachements will automatically be created when an email with a very specific subject is received. I used the coding:


Private Sub Application_NewMail()

Dim strattach As String
Dim myOlApp As Outlook.Application
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items


Set myOlApp = CreateObject("Outlook.Application")
Set OLNS = myOlApp.GetNamespace("MAPI")
Set myInbox = OLNS.GetDefaultFolder(olFolderInbox)
Set myitems = myInbox.Items

If myitems.Subject = "Send Cashflow" Then

Set MyMail = myitems.Reply
MyMail.To = "Email to be sent to Here"
MyMail.Subject = "Cashflow"
MyMail.Body = "Please find attached Cashflow"
Set myAttachments = MyMail.Attachments
strattach = "Attachment Link Here"
myAttachments.Add strattach, olByValue, 1
MyMail.Display
End If
MsgBox ("Cashflow has been sent."), , "Completed"

End Sub

I also had this code which i cut down to my details:

Private Sub Application_NewMail()

Dim strSender, strSubject, strBody, strattach As String
Set ol = GetObject(, "OUTLOOK.APPLICATION")
Set MAPI = ol.GetNamespace("MAPI")
Set mystore = MAPI.GetDefaultFolder(olFolderInbox)
Set myItems = myinbox.items
If myItem.Subject = "send cashflow" Then
Strattach = "Attachment Link Here"
Set MyMail = myItem.Reply
MyMail.To = "Email address here"
MyMail.Subject = "TCC Cashflow"
MyMail.Body = "Please find attached Cashflow"
Set myAttachments = MyMail.Attachments
myAttachments.Add strattach, olByValue, 1
MyMail.Display

MsgBox ("Cashflow has been sent."), , "Completed"
End If
End Sub

which actually worked however it created an email whenever an email was received regardless of the subject.

I then went on to another forum and a member helped me with this coding:


Private Sub Application_NewMail()
' In ThisOutlookSession module

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olFld As Outlook.MAPIFolder
Dim olMail As Outlook.mailItem

Debug.Print "Newmail triggered." 'Remove later if you get this working

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olFld = olNS.GetDefaultFolder(olFolderInbox)

olFld.Items.sort "Received", False
Set olMail = olFld.Items.GetFirst

SendCashflowReply olMail

Set olMail = Nothing
Set olFld = Nothing
Set olNS = Nothing
Set olApp = Nothing

End Sub

Private Sub SendCashflowReply(ByVal oMail As Outlook.mailItem)

Dim myMail As Outlook.mailItem

If InStr(1, LCase(oMail.Subject), "send cashflow") > 0 Then
Set myMail = oMail.reply
myMail.To = "Email to be sent to Here"
myMail.Subject = "Cashflow"
myMail.Body = "Please find attached Cashflow"

myMail.Display

MsgBox ("Reply has generated."), , "Completed"

Else

Debug.Print "No match: LCase(oMail.Subject) is " & LCase(oMail.Subject) 'Remove later if you get this working

End If
End Sub

But this does nothing, when an email with the specific subject is recieved, no email is created.

Can anyone help me please.

Thanks

 

[JP2112]

Welcome to the forum. Please use code tags when posting VBA:

[code]
... your code goes here ...
[/code]

To do something automatically when an email comes in, check out the ItemAdd Event. This event will fire every time an item is added to a folder you are monitoring.

http://www.jpsoftwaretech.com/outlook-vba/stock-event-code/

Ex:

Private WithEvents Items As Outlook.Items 
Private Sub Application_Startup() 
Dim olApp As Outlook.Application 
Dim objNS As Outlook.NameSpace 
Set olApp = Outlook.Application 
Set objNS = olApp.GetNamespace("MAPI") 
' (1) default Inbox 
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items 
End Sub
 
Private Sub Items_ItemAdd(ByVal item As Object) 
On Error Goto ErrorHandler 
Dim Msg As Outlook.MailItem 
' (2) only act if it's a MailItem 
If TypeName(item) = "MailItem" Then
Set Msg = item 
  If Msg.Subject = "Send Cashflow" Then
    ' code to create and send reply should be placed here
  End If
End If
ProgramExit: 
Exit Sub
ErrorHandler: 
MsgBox Err.Number & " - " & Err.Description 
Resume ProgramExit 
End Sub

After placing this code in ThisOutlookSession module, you must restart Outlook. You can test it by setting a breakpoint at the beginning of the ItemAdd Event and waiting for a message to arrive.

 

[Richardxtc]

Welcome to the forum. Please use code tags when posting VBA:

[code]
... your code goes here ...
[/code]

To do something automatically when an email comes in, check out the ItemAdd Event. This event will fire every time an item is added to a folder you are monitoring.

http://www.jpsoftwaretech.com/outlook-vba/stock-event-code/

Ex:

Private WithEvents Items As Outlook.Items 
Private Sub Application_Startup() 
Dim olApp As Outlook.Application 
Dim objNS As Outlook.NameSpace 
Set olApp = Outlook.Application 
Set objNS = olApp.GetNamespace("MAPI") 
' (1) default Inbox 
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items 
End Sub
 
Private Sub Items_ItemAdd(ByVal item As Object) 
On Error Goto ErrorHandler 
Dim Msg As Outlook.MailItem 
' (2) only act if it's a MailItem 
If TypeName(item) = "MailItem" Then
Set Msg = item 
  If Msg.Subject = "Send Cashflow" Then
    ' code to create and send reply should be placed here
  End If
End If
ProgramExit: 
Exit Sub
ErrorHandler: 
MsgBox Err.Number & " - " & Err.Description 
Resume ProgramExit 
End Sub

After placing this code in ThisOutlookSession module, you must restart Outlook. You can test it by setting a breakpoint at the beginning of the ItemAdd Event and waiting for a message to arrive.

Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
' (1) default Inbox
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
' (2) only act if it's a MailItem
If TypeName(item) = "MailItem" Then
Set Msg = item
If Msg.Subject = "send cashflow" Then
Set myMail = Msg.Reply
myMail.To = "Email to be sent to Here"
myMail.Subject = "Cashflow"
myMail.Body = "Please find attached Cashflow"
myMail.Display

End If
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub

this is what i came up with, still nothing happening tho! Could it perhaps be my Macro Secruity? I have it currently set to "No Security checks for Macros"

 

[JP2112]

Richardxtc, meet me halfway on this one!

The code you posted doesn't compile. Please compile your code before coming back and telling us it doesn't work.

The only spot in my code you should be editing is the part that says

' code to create and send reply should be placed here

You should replace this line with your code:

Dim myMail As Outlook.MailItem
Set myMail = Msg.Reply
myMail.To = "Email to be sent to Here"
myMail.Subject = "Cashflow"
myMail.Body = "Please find attached Cashflow"
myMail.Display

FYI, low macro security is discouraged, and it is not necessary. Simply (1) sign the VBA project, (2) restart Outlook and (3) accept the dialog to trust the project. I have instructions for how to do so.

http://www.jpsoftwaretech.com/outlook-vba/my-outlook-code-wont-run/

 

[Richardxtc]

Hmmm ok sorry, not even sure what you mean my code doesnt compile?

So i have done the Digital Signature part and the part where you said just replace

' code to create and send reply should be placed here

and it still does not automatically create a reply message...

see code i used below.

Im not sure if its something im doing or settings on outlook. Please bear in mind this would be the first time im doing a VBA in outlook so im an extreme noob.

Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
' (1) default Inbox
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
 
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
' (2) only act if it's a MailItem
If TypeName(item) = "MailItem" Then
Set Msg = item
  If Msg.Subject = "SEND" Then
Dim myMail As Outlook.MailItem
Set myMail = Msg.Reply
myMail.To = "Email to be sent to Here"
myMail.Subject = "Cashflow"
myMail.Body = "Please find attached Cashflow"
myMail.Display
  End If
End If
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub

 

[Derek Brown]

I don't think that you said which version of Outlook is being used.
There were a few changes to VBA in Outlook 2007.

 

[Richardxtc]

thanks guys did what i needed with a simple script

 

[DHruv001]

I don't think that you said which version of Outlook is being used.
There were a few changes to VBA in Outlook 2007.

Hello

i have gone through the discussion

i want to send email received with specific subject and auto send on specific email id

can if possible in outlook 2007 or 2010

awaiting for your valuable reply