Reply to Lotus Notes Email with VBA

PhilRJ

New Member
Joined
Jun 12, 2015
Messages
13
Hi Folks,

I've been scouring the next for this to no avail.
Any ideas as to how to use VBA to LOCATE an email received:

1) in the last 10 days;
2) by a specific sender (ex: abc@aol.com)
3) with a subject line containing a specific keywords (ex: "Order" and "Stock")

And REPLY to it with:

1) My own Subject Line (ex: Confirmation of Stock Order)
2) Sent to my own list of recipients
3) My own Body (insert text ex: "Dear Mr Smith...")

Thnx!

Phil
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,612
A few things to get you started:

Part 1 - get a NotesDocumentCollection or NotesView in the mail database and filter it using FTSearch and a filter string. Or simply loop through the collection or view looking for an email with the required property values (received time, sender, etc.).

Part 2 - see NotesDocument.CreateReplyMessage.
 

PhilRJ

New Member
Joined
Jun 12, 2015
Messages
13
John,

Thnx so much for the speedy reply.
I'll follow your tips.

Cheers,

Phil
 

PhilRJ

New Member
Joined
Jun 12, 2015
Messages
13
Hey,

I stitched together this piece of code.

Sub Click()
Dim session As New NotesSession
Dim db As NotesDatabase
Dim query As String
Dim collection As NotesDocumentCollection
Dim newsletter As NotesNewsletter
Dim doc As NotesDocument
Set db = session.CurrentDatabase
query = InputBox$ _
("What do you want to search for?", "Search")
Set collection = db.FTSearch(query, 10)
'Set notesNewsletter = New NotesNewsletter(collection)
Set doc = newsletter.FormatMsgWithDoclinks(db)
doc.Form = "Memo"
Call doc.Send(False, session.UserName)
End Sub

It looks promising but I'm unable to execute b/c this error comes up: run-time error 429. Activex can't create object.
I looked into it but hit a brick wall. Are there any specific VBA references (aside from the Lotus Domino and Lotus Automation Classes) that I should be aware of?

Phil
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,612
I think those are the only 2 VBA references available. Lotus Domino Objects are COM objects and Lotus Automation Classes are OLE, if that is any help. Objects such as NotesUIWorkspace are not supported in COM and you must declare the variable As Object and create it using the VBA CreateObject function.

PS please use CODE tags:

[CODE]
VBA code here
[/CODE]
 
Last edited:

PhilRJ

New Member
Joined
Jun 12, 2015
Messages
13
Thnx John. Truth is I'm pretty new to VBA and Lotusscript and I've struggled dearly the last couple of weeks with this. Are there are tweeks you would suggest to the code above that would result in a working code? Thnx so much. Phil
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,651
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top