Hi All,
I am trying to send emails through Lotus notes.Issue here is I want to send it from my group mail box insted of my personal.
Please find my code below. I tried few stuffs but its not helping.Please let me know if any idea.
Sub Send_HTML_Email()
Const ENC_IDENTITY_8BIT = 1729
'Send Lotus Notes email containing links to files on local computer
Dim NSession As Object 'NotesSession
Dim NDatabase As Object 'NotesDatabase
Dim NStream As Object 'NotesStream
Dim NDoc As Object 'NotesDocument
Dim NMIMEBody As Object 'NotesMIMEEntity
Dim SendTo As String
Dim Subject As String
Dim html As String, HTMLbody As String
Dim lstrow As Integer
lstrow = ActiveWorkbook.Sheets(1).Cells(Application.Rows.Count, 1).End(xlUp).Row
IDlist = ActiveWorkbook.Sheets(1).Range("C2:C" & lstrow).Value
UIDlist = ActiveWorkbook.Sheets(1).Range("E2:E" & lstrow).Value
UserList = ActiveWorkbook.Sheets(1).Range("A2:A" & lstrow).Value
For i = 1 To UBound(IDlist)
j = i
c = i
IDname = UCase(IDlist(i, 1))
UserId = UCase(UIDlist(j, 1))
UIDname = UCase(UserList(c, 1))
SendTo = IDname
Subject = "FTP Inactive User Reminder - " & Format(Now(), "mm") & "/" & Format(Now(), "dd")
' Debug.Print subject
Set NSession = CreateObject("Notes.NotesSession") 'using Lotus Notes Automation Classes (OLE)
Set NDatabase = NSession.GETDATABASE("", "")
'Server = NSession.GetEnvironmentString("MailServer", True) <--- thes steps I was trying to use to get the server name of the group box
'Set NDatabase = NSession.GETDATABASE("CN=IN-MAIL-144/OU=SERVERS/O=HSBC", "mail\ap416236.nfs") <---here am using the file name and the server.
If Not NDatabase.IsOpen Then NDatabase.OpenMail
Set NStream = NSession.CreateStream
HTMLbody = "<br>Hi,<br/>" & "<br>Kindly Login to FTP.<br/>" & "<br>The following HSBC Intranet user account that has access to: FTP - <b>Finance Transformation Platform</b><br/>" & _
"<br>(FTP provides us with a single source of data for financial control and supports key elements of the Global Finance vision and strategy)<br/>" & _
"<br>User Name: " & UIDname & "<br/>" & "<br>User ID: " & UserId & "<br/>" & "<p>Will be inactivated after 5 days. </p>" & "<b>Please sign-on, in order to avoid user account inactivation.</b>" & _
"<br>Click " & "<a href='https://finance-platform.us.hsbc/psp/PORP02/FINANCE/ENTP/h/?tab=Z_FINANCE_PUBLIC'> <b>click</b></a>" & " to Sign-on to Portal.<br/>" & _
"<br>Users should contact the FTP Help Desk with all questions and issues. <br/>" & _
"Telephone 1-866-670-2758 (toll free), or " & _
"<br>Email:US FTP HelpDesk/HBUS/HSBC. <br/>" & _
"<hr width=40% align=left>This is a system-generated message. Please DO NOT REPLY. </hr>" & _
"<hr width=40% align=left></hr>"
html = "<html>" & vbLf & _
"<head>" & vbLf & _
"****** http-equiv=""Content-Type"" content=""text/html; charset=UTF-8""/>" & vbLf & _
"</head>" & vbLf & _
"<body>" & vbLf & _
"<font face=Verdana>" & vbLf & _
"<font size=2>" & vbLf & _
HTMLbody & _
"</font>" & vbLf & _
"</font>" & vbLf & _
"</body>" & vbLf & _
"</html>"
NSession.ConvertMIME = False 'Don't convert MIME to rich text
Set NDoc = NDatabase.CreateDocument()
With NDoc
.Form = "Memo"
.Subject = Subject
.SendTo = Split(SendTo, ",")
Set NMIMEBody = .CreateMIMEEntity
NStream.WRITETEXT html
NMIMEBody.SETCONTENTFROMTEXT NStream, "text/html; charset=UTF-8", ENC_IDENTITY_8BIT
.Send False
.Save True, False, False
End With
NSession.ConvertMIME = True 'Restore conversion
Set NDoc = Nothing
Set NSession = Nothing
Next i
MsgBox "Emails sent successfully..!", vbInformation
End Sub
I am trying to send emails through Lotus notes.Issue here is I want to send it from my group mail box insted of my personal.
Please find my code below. I tried few stuffs but its not helping.Please let me know if any idea.
Sub Send_HTML_Email()
Const ENC_IDENTITY_8BIT = 1729
'Send Lotus Notes email containing links to files on local computer
Dim NSession As Object 'NotesSession
Dim NDatabase As Object 'NotesDatabase
Dim NStream As Object 'NotesStream
Dim NDoc As Object 'NotesDocument
Dim NMIMEBody As Object 'NotesMIMEEntity
Dim SendTo As String
Dim Subject As String
Dim html As String, HTMLbody As String
Dim lstrow As Integer
lstrow = ActiveWorkbook.Sheets(1).Cells(Application.Rows.Count, 1).End(xlUp).Row
IDlist = ActiveWorkbook.Sheets(1).Range("C2:C" & lstrow).Value
UIDlist = ActiveWorkbook.Sheets(1).Range("E2:E" & lstrow).Value
UserList = ActiveWorkbook.Sheets(1).Range("A2:A" & lstrow).Value
For i = 1 To UBound(IDlist)
j = i
c = i
IDname = UCase(IDlist(i, 1))
UserId = UCase(UIDlist(j, 1))
UIDname = UCase(UserList(c, 1))
SendTo = IDname
Subject = "FTP Inactive User Reminder - " & Format(Now(), "mm") & "/" & Format(Now(), "dd")
' Debug.Print subject
Set NSession = CreateObject("Notes.NotesSession") 'using Lotus Notes Automation Classes (OLE)
Set NDatabase = NSession.GETDATABASE("", "")
'Server = NSession.GetEnvironmentString("MailServer", True) <--- thes steps I was trying to use to get the server name of the group box
'Set NDatabase = NSession.GETDATABASE("CN=IN-MAIL-144/OU=SERVERS/O=HSBC", "mail\ap416236.nfs") <---here am using the file name and the server.
If Not NDatabase.IsOpen Then NDatabase.OpenMail
Set NStream = NSession.CreateStream
HTMLbody = "<br>Hi,<br/>" & "<br>Kindly Login to FTP.<br/>" & "<br>The following HSBC Intranet user account that has access to: FTP - <b>Finance Transformation Platform</b><br/>" & _
"<br>(FTP provides us with a single source of data for financial control and supports key elements of the Global Finance vision and strategy)<br/>" & _
"<br>User Name: " & UIDname & "<br/>" & "<br>User ID: " & UserId & "<br/>" & "<p>Will be inactivated after 5 days. </p>" & "<b>Please sign-on, in order to avoid user account inactivation.</b>" & _
"<br>Click " & "<a href='https://finance-platform.us.hsbc/psp/PORP02/FINANCE/ENTP/h/?tab=Z_FINANCE_PUBLIC'> <b>click</b></a>" & " to Sign-on to Portal.<br/>" & _
"<br>Users should contact the FTP Help Desk with all questions and issues. <br/>" & _
"Telephone 1-866-670-2758 (toll free), or " & _
"<br>Email:US FTP HelpDesk/HBUS/HSBC. <br/>" & _
"<hr width=40% align=left>This is a system-generated message. Please DO NOT REPLY. </hr>" & _
"<hr width=40% align=left></hr>"
html = "<html>" & vbLf & _
"<head>" & vbLf & _
"****** http-equiv=""Content-Type"" content=""text/html; charset=UTF-8""/>" & vbLf & _
"</head>" & vbLf & _
"<body>" & vbLf & _
"<font face=Verdana>" & vbLf & _
"<font size=2>" & vbLf & _
HTMLbody & _
"</font>" & vbLf & _
"</font>" & vbLf & _
"</body>" & vbLf & _
"</html>"
NSession.ConvertMIME = False 'Don't convert MIME to rich text
Set NDoc = NDatabase.CreateDocument()
With NDoc
.Form = "Memo"
.Subject = Subject
.SendTo = Split(SendTo, ",")
Set NMIMEBody = .CreateMIMEEntity
NStream.WRITETEXT html
NMIMEBody.SETCONTENTFROMTEXT NStream, "text/html; charset=UTF-8", ENC_IDENTITY_8BIT
.Send False
.Save True, False, False
End With
NSession.ConvertMIME = True 'Restore conversion
Set NDoc = Nothing
Set NSession = Nothing
Next i
MsgBox "Emails sent successfully..!", vbInformation
End Sub
Last edited:
