Automatic mail sending from lotus mail on the basis of data received in the email

[Rahulkr]

Dear All,

How are you?

Currently , I am working on this following project and was able to achieve the target about 50 percent on click of buttons by assigning particular codes, but not as desired to do it automatically.

Below is the requirement.

If a particular mail from a particular mail id ie.(abc@gmail.com) arrives in the inbox of lotus mail on current date and time of the system. (macro needs to runs itself continuously after every 1 minute interval of time to check that particular mail is arrived in the inbox from particular mail id or not. (unable to get idea on this on how to do)

If mail arrived from that particular mail id, then macro will run automatically to read the text body of the mail and get full body text inserted in the (Email) Sheet. After that I have segregated some data with the help of trim formula to extract the particular line or particular text in email body ie. (Customer code : 50028516, Instruction Type : Payment, Number of Instructions : 1, which is unique data) and then the number part (ie. 50028516) will be inserted in customer code column of (Main) Sheet with the help of “=VALUE(Email!G8)”.

After that with the help of vlookup formula customer name is inserted in the (Main) Sheet customer name column and similarly transaction type and number of transactions were inserted from the (Email Sheet) into the Main Sheet with the helpf of =value formula.

Pre-created excel file format

IN (Main) Sheet:-

Customer Code	Customer Name	Transaction Type	Number Transactions
50028516	Monster india private llimited	Payment	1

Here in customer name column Vlookup formula inserted to fetch data from Data Table and in rest of column i.e. Customer code, transaction type, and number of transactions (=VALUE(Email!G7), =Value(Email!G5), =VALUE(Email!G8)) is inserted to get the data from (Email) Sheet.

With one button (Get Mail) with assigned code to fetch the mail and fill email sheet.

IN (Data)Sheet

Customer Code	Customer Name
30028518	Turbojet india private limted
30528417	Ymleasdfas company ltd
50028516	Monster india private llimited
46852555	dfffs company ltd
55554258	dfdefdfr india private llimited
32565684	sdfdfdf company ltd

In (Email) sheet:-

A	B	C	D	E	F	G	H	I	J	K	L
								Code :	Instructions :		Type :
Instruction Type : Payment						Payment
Instructed Branch Unit Code : 54545445
Customer Unit Code : 50028516						50028516
Number of Instructions : 1						1

In this I have used some formula to extract and segregate the data from the particular lines.

=RIGHT(A5,LEN(A5)-FIND($L$1,A5)-8) to get the word payment

=RIGHT(A7,LEN(A7)-FIND($I$1,A7)-8) to get the number

=RIGHT(A8,LEN(A8)-FIND($J$1,A8)-17) to get the number


code for reading the mail and paste it in the excel sheet(Email)

Public Sub Lotus_Notes_Current_Email2()
Dim NSession As Object 'NotesSession
Dim NUIWorkspace As Object 'NotesUIWorkspace
Dim NUIDoc As Object 'NotesUIDocument
Dim NItem As Object 'NotesItem
Dim lines As Variant

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")

Set NUIDoc = NUIWorkspace.CurrentDocument
If Not NUIDoc Is Nothing Then
With NUIDoc.Document
Set NItem = .GetFirstItem("Body")
If Not NItem Is Nothing Then
lines = Split(NItem.Text, vbCrLf)
Sheets("Email").Range("A1").Resize(UBound(lines) + 1, 1).Value = Application.WorksheetFunction.Transpose(lines)
End If
End With
Else
MsgBox "Lotus Notes is not displaying an email"
End If

Set NUIDoc = Nothing
Set NUIWorkspace = Nothing
Set NSession = Nothing

End Sub

Code to generate the mail as by your provided code john.

Code:

Public Sub Send_Lotus_Email2()

Dim NSession As Object
Dim NWorkspace As Object
Dim NMailDb As Object
Dim NUIDocument As Object
Dim Subject As String
Dim SendTo As String, CopyTo As String, BODYeX As String
Dim attachmentFile As String
Dim embedCells As Range
Dim lastCellRowNumber As Long


'------------ User-defined settings section ------------


SendTo = "rahul.rana@gmail.com"

CopyTo = "kumari.rohni@gmail.com"

Subject = "Transactions of the customers"
BODYeX = "Transaction Details are as follows:-"



With Worksheets("Main")

Set embedCells = .Columns("A:D")

End With

'------------ End of user-defined settings ------------

Set NSession = CreateObject("Notes.NotesSession") 'OLE, late binding only
Set NWorkspace = CreateObject("Notes.NotesUIWorkspace")

Set NMailDb = NSession.GetDatabase("", "")
NMailDb.OpenMail

NWorkspace.ComposeDocument , , "Memo"

Set NUIDocument = NWorkspace.CurrentDocument

With NUIDocument
.FieldSetText "EnterSendTo", SendTo
.FieldSetText "EnterCopyTo", CopyTo
.FieldSetText "EnterBlindCopyTo", ""
.FieldSetText "Subject", Subject
.GotoField "Body"

'------------ Start of email body text ------------

.InsertText " ", BODYeX

Sheets("Main").Range("A1:D2").Copy 'CHANGE THIS
.Paste




End With


Set NUIDocument = Nothing
Set NWorkspace = Nothing
Set NMailDb = Nothing
Set NSession = Nothing

End Sub

Is it possible to do it automatically, then please help me out John.

 

[Rahulkr]

Please help me out on this..................help will be appreciated a lot.