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

Rahulkr

Board Regular
Joined
Dec 10, 2019
Messages
66
Office Version
  1. 2010
Platform
  1. Windows
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 CodeCustomer NameTransaction TypeNumber Transactions
50028516Monster india private llimitedPayment1

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 CodeCustomer Name
30028518Turbojet india private limted
30528417Ymleasdfas company ltd
50028516Monster india private llimited
46852555dfffs company ltd
55554258dfdefdfr india private llimited
32565684sdfdfdf company ltd


In (Email) sheet:-



ABCDEFGHIJKL
Code :Instructions :Type :
Instruction Type : PaymentPayment
Instructed Branch Unit Code : 54545445
Customer Unit Code : 5002851650028516
Number of Instructions : 11


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)
VBA Code:
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:
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Please help me out on this..................help will be appreciated a lot.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top