Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron, I found some code that works with Lotus Notes
Ron, I found this and I've been able to get it to work (the way I need it to)
with Lotus Notes. I know there have been many questions on this site about using Lotus Notes so if it will help anyone else out there, tailor it to your needs It would be great to have on your site, where I can refer back to it, often... Thanks for all your assistance in the past, and in the future. ' Creates statement for employee and emails it to Data-Entry Sub LotusMail() ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment1 As String ' setting up all sending recipients recipient = "data" ccRecipient = "" bccRecipient = "" ' Gets employye name and incident number to create subjest line subject = "Statement for " & Sheets("Employee List").Range("N5").Value & " for Incident " & Sheets("Employee List").Range("N7").Value ' Gets Statement created and puts in body of email bodytext = ThisWorkbook.Worksheets("Employee List").Range("N4").Value '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation Exit Sub End If ' creating a notes session Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen < True Then On Error Resume Next Maildb.OPENMAIL End If Set MailDoc = Maildb.CreateDocument MailDoc.form = "Memo" ' loading the lotus notes e-mail with the inputed data With MailDoc ..sendto = recipient ..copyto = ccRecipient ..blindcopyto = bccRecipient ..subject = subject ..body = bodytext End With ' saving message MailDoc.SaveMessageOnSend = True Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value If Attachment1 < "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1") Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If ' send e-mail !!!! MailDoc.PostedDate = Now() ' if error in attachment or name of recipients On Error GoTo errorhandler1 MailDoc.Send 0, recipient Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing 'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists" Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron, I found some code that works with Lotus Notes
Hi Tim
Thanks for the info I have a few other examples also but want to test it myself first before I make a page about it. I contact the Lotus Notes team also a few months back but no reaction from them. I will send a mail today to them again I will download a trial version of Lotus Notes and run it on a Virtal PC soon. I never used it myself. Can you tell me about the most used version ?? -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... Ron, I found this and I've been able to get it to work (the way I need it to) with Lotus Notes. I know there have been many questions on this site about using Lotus Notes so if it will help anyone else out there, tailor it to your needs It would be great to have on your site, where I can refer back to it, often... Thanks for all your assistance in the past, and in the future. ' Creates statement for employee and emails it to Data-Entry Sub LotusMail() ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment1 As String ' setting up all sending recipients recipient = "data" ccRecipient = "" bccRecipient = "" ' Gets employye name and incident number to create subjest line subject = "Statement for " & Sheets("Employee List").Range("N5").Value & " for Incident " & Sheets("Employee List").Range("N7").Value ' Gets Statement created and puts in body of email bodytext = ThisWorkbook.Worksheets("Employee List").Range("N4").Value '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation Exit Sub End If ' creating a notes session Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen < True Then On Error Resume Next Maildb.OPENMAIL End If Set MailDoc = Maildb.CreateDocument MailDoc.form = "Memo" ' loading the lotus notes e-mail with the inputed data With MailDoc .sendto = recipient .copyto = ccRecipient .blindcopyto = bccRecipient .subject = subject .body = bodytext End With ' saving message MailDoc.SaveMessageOnSend = True Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value If Attachment1 < "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1") Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If ' send e-mail !!!! MailDoc.PostedDate = Now() ' if error in attachment or name of recipients On Error GoTo errorhandler1 MailDoc.Send 0, recipient Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing 'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists" Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron, I found some code that works with Lotus Notes
They are using 6.5 at my work. I work for a municipal government and we have
all municipal employees on it, including police and fire departments, but I use Outlook at home, Bill ;). "Ron de Bruin" wrote: Hi Tim Thanks for the info I have a few other examples also but want to test it myself first before I make a page about it. I contact the Lotus Notes team also a few months back but no reaction from them. I will send a mail today to them again I will download a trial version of Lotus Notes and run it on a Virtal PC soon. I never used it myself. Can you tell me about the most used version ?? -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... Ron, I found this and I've been able to get it to work (the way I need it to) with Lotus Notes. I know there have been many questions on this site about using Lotus Notes so if it will help anyone else out there, tailor it to your needs It would be great to have on your site, where I can refer back to it, often... Thanks for all your assistance in the past, and in the future. ' Creates statement for employee and emails it to Data-Entry Sub LotusMail() ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment1 As String ' setting up all sending recipients recipient = "data" ccRecipient = "" bccRecipient = "" ' Gets employye name and incident number to create subjest line subject = "Statement for " & Sheets("Employee List").Range("N5").Value & " for Incident " & Sheets("Employee List").Range("N7").Value ' Gets Statement created and puts in body of email bodytext = ThisWorkbook.Worksheets("Employee List").Range("N4").Value '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation Exit Sub End If ' creating a notes session Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen < True Then On Error Resume Next Maildb.OPENMAIL End If Set MailDoc = Maildb.CreateDocument MailDoc.form = "Memo" ' loading the lotus notes e-mail with the inputed data With MailDoc .sendto = recipient .copyto = ccRecipient .blindcopyto = bccRecipient .subject = subject .body = bodytext End With ' saving message MailDoc.SaveMessageOnSend = True Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value If Attachment1 < "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1") Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If ' send e-mail !!!! MailDoc.PostedDate = Now() ' if error in attachment or name of recipients On Error GoTo errorhandler1 MailDoc.Send 0, recipient Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing 'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists" Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron, I found some code that works with Lotus Notes
Thanks Tim
I will phone the Dutch office today to get more information -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... They are using 6.5 at my work. I work for a municipal government and we have all municipal employees on it, including police and fire departments, but I use Outlook at home, Bill ;). "Ron de Bruin" wrote: Hi Tim Thanks for the info I have a few other examples also but want to test it myself first before I make a page about it. I contact the Lotus Notes team also a few months back but no reaction from them. I will send a mail today to them again I will download a trial version of Lotus Notes and run it on a Virtal PC soon. I never used it myself. Can you tell me about the most used version ?? -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... Ron, I found this and I've been able to get it to work (the way I need it to) with Lotus Notes. I know there have been many questions on this site about using Lotus Notes so if it will help anyone else out there, tailor it to your needs It would be great to have on your site, where I can refer back to it, often... Thanks for all your assistance in the past, and in the future. ' Creates statement for employee and emails it to Data-Entry Sub LotusMail() ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment1 As String ' setting up all sending recipients recipient = "data" ccRecipient = "" bccRecipient = "" ' Gets employye name and incident number to create subjest line subject = "Statement for " & Sheets("Employee List").Range("N5").Value & " for Incident " & Sheets("Employee List").Range("N7").Value ' Gets Statement created and puts in body of email bodytext = ThisWorkbook.Worksheets("Employee List").Range("N4").Value '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation Exit Sub End If ' creating a notes session Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen < True Then On Error Resume Next Maildb.OPENMAIL End If Set MailDoc = Maildb.CreateDocument MailDoc.form = "Memo" ' loading the lotus notes e-mail with the inputed data With MailDoc .sendto = recipient .copyto = ccRecipient .blindcopyto = bccRecipient .subject = subject .body = bodytext End With ' saving message MailDoc.SaveMessageOnSend = True Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value If Attachment1 < "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1") Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If ' send e-mail !!!! MailDoc.PostedDate = Now() ' if error in attachment or name of recipients On Error GoTo errorhandler1 MailDoc.Send 0, recipient Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing 'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists" Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron, I found some code that works with Lotus Notes
If you don't have any luck with them and need someone to run some test. I
will be glad to assist, although my knowledge is limited. It's the least I can do for your past assistance. "Ron de Bruin" wrote: Thanks Tim I will phone the Dutch office today to get more information -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... They are using 6.5 at my work. I work for a municipal government and we have all municipal employees on it, including police and fire departments, but I use Outlook at home, Bill ;). "Ron de Bruin" wrote: Hi Tim Thanks for the info I have a few other examples also but want to test it myself first before I make a page about it. I contact the Lotus Notes team also a few months back but no reaction from them. I will send a mail today to them again I will download a trial version of Lotus Notes and run it on a Virtal PC soon. I never used it myself. Can you tell me about the most used version ?? -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... Ron, I found this and I've been able to get it to work (the way I need it to) with Lotus Notes. I know there have been many questions on this site about using Lotus Notes so if it will help anyone else out there, tailor it to your needs It would be great to have on your site, where I can refer back to it, often... Thanks for all your assistance in the past, and in the future. ' Creates statement for employee and emails it to Data-Entry Sub LotusMail() ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment1 As String ' setting up all sending recipients recipient = "data" ccRecipient = "" bccRecipient = "" ' Gets employye name and incident number to create subjest line subject = "Statement for " & Sheets("Employee List").Range("N5").Value & " for Incident " & Sheets("Employee List").Range("N7").Value ' Gets Statement created and puts in body of email bodytext = ThisWorkbook.Worksheets("Employee List").Range("N4").Value '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation Exit Sub End If ' creating a notes session Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen < True Then On Error Resume Next Maildb.OPENMAIL End If Set MailDoc = Maildb.CreateDocument MailDoc.form = "Memo" ' loading the lotus notes e-mail with the inputed data With MailDoc .sendto = recipient .copyto = ccRecipient .blindcopyto = bccRecipient .subject = subject .body = bodytext End With ' saving message MailDoc.SaveMessageOnSend = True Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value If Attachment1 < "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1") Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If ' send e-mail !!!! MailDoc.PostedDate = Now() ' if error in attachment or name of recipients On Error GoTo errorhandler1 MailDoc.Send 0, recipient Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing 'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists" Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron, I found some code that works with Lotus Notes
Hi Tim
If you don't have any luck with them and need someone to run some test Thanks Send me a private mail then I can contact you if I have something -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... If you don't have any luck with them and need someone to run some test. I will be glad to assist, although my knowledge is limited. It's the least I can do for your past assistance. "Ron de Bruin" wrote: Thanks Tim I will phone the Dutch office today to get more information -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... They are using 6.5 at my work. I work for a municipal government and we have all municipal employees on it, including police and fire departments, but I use Outlook at home, Bill ;). "Ron de Bruin" wrote: Hi Tim Thanks for the info I have a few other examples also but want to test it myself first before I make a page about it. I contact the Lotus Notes team also a few months back but no reaction from them. I will send a mail today to them again I will download a trial version of Lotus Notes and run it on a Virtal PC soon. I never used it myself. Can you tell me about the most used version ?? -- Regards Ron de Bruin http://www.rondebruin.nl "Tim" wrote in message ... Ron, I found this and I've been able to get it to work (the way I need it to) with Lotus Notes. I know there have been many questions on this site about using Lotus Notes so if it will help anyone else out there, tailor it to your needs It would be great to have on your site, where I can refer back to it, often... Thanks for all your assistance in the past, and in the future. ' Creates statement for employee and emails it to Data-Entry Sub LotusMail() ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment1 As String ' setting up all sending recipients recipient = "data" ccRecipient = "" bccRecipient = "" ' Gets employye name and incident number to create subjest line subject = "Statement for " & Sheets("Employee List").Range("N5").Value & " for Incident " & Sheets("Employee List").Range("N7").Value ' Gets Statement created and puts in body of email bodytext = ThisWorkbook.Worksheets("Employee List").Range("N4").Value '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation Exit Sub End If ' creating a notes session Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen < True Then On Error Resume Next Maildb.OPENMAIL End If Set MailDoc = Maildb.CreateDocument MailDoc.form = "Memo" ' loading the lotus notes e-mail with the inputed data With MailDoc .sendto = recipient .copyto = ccRecipient .blindcopyto = bccRecipient .subject = subject .body = bodytext End With ' saving message MailDoc.SaveMessageOnSend = True Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value If Attachment1 < "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1") Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If ' send e-mail !!!! MailDoc.PostedDate = Now() ' if error in attachment or name of recipients On Error GoTo errorhandler1 MailDoc.Send 0, recipient Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing 'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists" Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lotus Notes | Excel Discussion (Misc queries) | |||
Lotus Notes Stationery | Excel Programming | |||
Excel/Lotus Notes | Excel Programming | |||
Lotus Notes | Excel Programming | |||
Can you use code to Send excel spreadsheets via Lotus Notes?? | Excel Programming |