M
m@
I am new to this, but was told all my life, "There is no such thing as
a stupid question." So here goes....
I work in a large company, and currently, as a company, we are running
different version of Lotus Notes. I currently have Lotus Notes 5.09a
and we are running upto 6.0 and higher. I have been working in VBA
(Excel and Access) and was given some code that would send an email
from excel to Lotus Notes via VBA. On my machine, the code is perfect
- no problems at all. I then had someone test my model with Lotus
Notes 6.0a. The code did not work.
To be specific, the code creates and sends an excel speadsheet to
lotus notes. If all works well, the user ends up with a message in
their inbox with an attached file and I am cc'd in on the message.
With Lotus Notes 6, the code runs, creates the file and copies me in.
However, it does not send an email to the user's account. In fact the
"To:" line is left blank. I have tried this on 3 different Lotus 6.0
accounts, to the same result.
I'm not at the point in my coding life that allows me to debug this.
Anyone's help would be great!
Sample Code
' get path of current workbook
pth = ThisWorkbook.Path
' get file from same path
fil = pth & "\ExportFile.xls"
'On Error GoTo ErrorGROopenGRO_NB_worksheet
ThisWorkbook.Sheets("ExportFile").Range _
("ExportFile").Copy
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.Workbooks.Open fil, True
xlApp.Range("A1").PasteSpecial Paste:=xlValues
xlApp.Application.Run ("'" & fil &
"'!module1.NameandClose")
Set xlApp = Nothing
PS = Sheet8.Range("E8"): qt = Chr$(34)
AsSc = Sheet8.Range("E14")
AE = Sheet1.Range("H7"): qt = Chr$(34)
FilName = PS & "_" & AsSc
EmailName = Sheet8.Range("m3"): qt = Chr$(34)
'Create a Lotus Notes session object:
Set s = CreateObject("Notes.Notessession")
'Point to the current database:
Set db = s.GETDATABASE("", "")
'Open the default mail database:
db.OPENMAIL
'Create the document object:
Set doc = db.CREATEDOCUMENT
'You can set the following properties of the document object:
'Subject - this is just a text string:
doc.Subject = "Pricing request for " & AE & " - " & PS & ""
'Body - this holds the contents of your e-mail:
doc.Body = "Please review the following information."
'CopyTo - this holds the list of people that you want to carbon
copy the note to:
doc.CopyTo = "me"
'If you want to attach a spreadsheet or word document you would
'set the following objects:
'You create a rich text item:
Set rt = doc.CREATERICHTEXTITEM("Attachment")
'You then pass the rich text object the path to the file:
Set obj = rt.EMBEDOBJECT(1454, "", "C:\Temp\" & FilName & ".xls")
'The next to last item is to set up the list of people that
'you want to send the note to. You do this through a string array.
'You can dimension the array as follows:
'Then populate each element of the array with the name of the
person receiving the note:
SendList = EmailName
' if needed you can have more names, or set up loop
'SendList(2) = "Tom Hanks"...
'The last step is to actually send the note:
doc.Send False, SendList
'And then clear all your objects:
SendEmailtoHeadOffice = True
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Exit Function
a stupid question." So here goes....
I work in a large company, and currently, as a company, we are running
different version of Lotus Notes. I currently have Lotus Notes 5.09a
and we are running upto 6.0 and higher. I have been working in VBA
(Excel and Access) and was given some code that would send an email
from excel to Lotus Notes via VBA. On my machine, the code is perfect
- no problems at all. I then had someone test my model with Lotus
Notes 6.0a. The code did not work.
To be specific, the code creates and sends an excel speadsheet to
lotus notes. If all works well, the user ends up with a message in
their inbox with an attached file and I am cc'd in on the message.
With Lotus Notes 6, the code runs, creates the file and copies me in.
However, it does not send an email to the user's account. In fact the
"To:" line is left blank. I have tried this on 3 different Lotus 6.0
accounts, to the same result.
I'm not at the point in my coding life that allows me to debug this.
Anyone's help would be great!
Sample Code
' get path of current workbook
pth = ThisWorkbook.Path
' get file from same path
fil = pth & "\ExportFile.xls"
'On Error GoTo ErrorGROopenGRO_NB_worksheet
ThisWorkbook.Sheets("ExportFile").Range _
("ExportFile").Copy
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.Workbooks.Open fil, True
xlApp.Range("A1").PasteSpecial Paste:=xlValues
xlApp.Application.Run ("'" & fil &
"'!module1.NameandClose")
Set xlApp = Nothing
PS = Sheet8.Range("E8"): qt = Chr$(34)
AsSc = Sheet8.Range("E14")
AE = Sheet1.Range("H7"): qt = Chr$(34)
FilName = PS & "_" & AsSc
EmailName = Sheet8.Range("m3"): qt = Chr$(34)
'Create a Lotus Notes session object:
Set s = CreateObject("Notes.Notessession")
'Point to the current database:
Set db = s.GETDATABASE("", "")
'Open the default mail database:
db.OPENMAIL
'Create the document object:
Set doc = db.CREATEDOCUMENT
'You can set the following properties of the document object:
'Subject - this is just a text string:
doc.Subject = "Pricing request for " & AE & " - " & PS & ""
'Body - this holds the contents of your e-mail:
doc.Body = "Please review the following information."
'CopyTo - this holds the list of people that you want to carbon
copy the note to:
doc.CopyTo = "me"
'If you want to attach a spreadsheet or word document you would
'set the following objects:
'You create a rich text item:
Set rt = doc.CREATERICHTEXTITEM("Attachment")
'You then pass the rich text object the path to the file:
Set obj = rt.EMBEDOBJECT(1454, "", "C:\Temp\" & FilName & ".xls")
'The next to last item is to set up the list of people that
'you want to send the note to. You do this through a string array.
'You can dimension the array as follows:
'Then populate each element of the array with the name of the
person receiving the note:
SendList = EmailName
' if needed you can have more names, or set up loop
'SendList(2) = "Tom Hanks"...
'The last step is to actually send the note:
doc.Send False, SendList
'And then clear all your objects:
SendEmailtoHeadOffice = True
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Exit Function