?
Solved

Send An Email From Excel Via Lotus Notes

Posted on 2003-03-20
16
Medium Priority
?
1,660 Views
Last Modified: 2012-05-04
I am trying to send an email from Excel using Lotus Notes.  My code at the moment stands as:

Sub Email()
Dim MyDate, MyStr
    MyDate = Date
    MyStr = Format(Date, "Short Date")
    MyStr = Format(MyDate, "dd-mm-yy")
emailsubject = UserForm1.TextBox2.Value & " Sales Stats " & MyStr
emailrecipient = UserForm1.TextBox2.Value
fdir = "C:\WINDOWS\DESKTOP\"
fname = emailrecipient & " " & MyStr
If emailrecipient = "" Then
MsgBox ("Your email failed to send, please ensure you have completed all details correctly")
Exit Sub
End If
If Application.MailSystem <> xlNoMailSystem And emailrecipient <> "" Then
Worksheets("Parsed And Sorted Sales List").Select
With ActiveSheet
.Range("C1").AutoFilter Field:=3, Criteria1:=UserForm1.TextBox2.Value
.Range("C1").SpecialCells(xlCellTypeVisible).EntireRow.Copy
End With
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
ChDir "C:\WINDOWS\DESKTOP\Backup Of Stats\"
With ActiveWorkbook
.SaveAs Filename:=fdir & emailrecipient & MyStr
.SendMail Recipients:="Robert Cunningham", Subject:=emailsubject
.Close SaveChanges:=False
End With
Application.MailLogoff
Exit Sub
Else
MsgBox "You must specify a valid e-mail address in order to send the sheet via e-mail."
Exit Sub
End If
End Sub

I previously used Groupwise as my default e-mail client and this worked OK - is there a problem with the above code which will not allow Lotus to send?

Does anyone know a workaround which perhaps focuses on Lotus Notes application and then sends the e-mail.

I am completely lost!!!

Thanks
0
Comment
Question by:cantonexpress
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8176984
Is it giving any error? If so where?
0
 

Author Comment

by:cantonexpress
ID: 8180501
error is

Runtime Error '1004'
General Mail Failure. Quit Microsoft Excel, Restart The mail System, and try again.

thx
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184523
Are you sure that your mail system is running (basically do you know if your mail server is on?)

I don't know much about how the Lotus Notes mail system works.

Also where does the error occur. Place a breakpoint in the beggining of the code and when it stops, keep pressing F8 till you get the error. Then please say what line is causing the error. This information can be important for someone trying to help...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:cantonexpress
ID: 8198406
Error is occuring at

.SendMail Recipients:="Robert Cunningham", Subject:=emailsubject

How can I ensure that the mail system is running ?  Operating system is Windows XP alongside Lotus Notes.

thx
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8198627
Hi,

Taking a very quick look for today, will look back tomorrow if you have no luck inbetween...

Firstly, can you send a sheet or word document etc. using File | SendTo in the normal way?

If you go into IE and have a look at the mail client (Tools | Internet Options | Programs and check the Email client) does it say Lotus Notes?

In Notes, File | Preferences | User preferences.  In the list of options scroll through and see if you can see "Office 97 SendTo" and check it if it is there.  Unfortunately this option is hidden if you have a later version of Office installed (or part of it such as Outlook 2000), that can be got around...

Have you tried it with the user having notes open and with password alreay typed in. (Some people use network based notes.ini files etc. to control Notes but do not change the file associations and notes mail shortcuts).

What version Notes client are you using (presumably R5?) and same for Office?

The code seems to work for me OK (except not finding the recipient, have you tried other formats of names (e.g. internet format).  I have various other bits of code for creating a document in Notes if needed...

As I said, very quick reply as bed is calling.  Let me know some or all of above and I'll see I can assist properly tomorrow!

Steve
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8198680
Thanks for droping by Steve. I'm sorry. When I called you I forgot that even though we share a time zone we don't necessarilly share the same bedtime :) But thanks just the same.

cantonexpress, as I said I don't know much about Lotus Notes. Hopefully Steve will be able to assist you more than I ever would. You're in good hands now (hmmm... not that you weren't before... Just not as good, that's it :) at least not in this case)

I'll be assisting this with interest. Maybe I'll also learn something.

Paulo
0
 

Author Comment

by:cantonexpress
ID: 8198845
Steve / Paulo

Thanks guys for your help tonight.  I will check Paulo's suggestions at work tomorrow.

Get back to you as soon as I can.

Oh and the recipient point -  I have just added this name as an example - in the final workbook this will be a variable which the user selects from a dynamic named range which is in one of the worksheets.

Cheers

Robert
0
 
LVL 44

Expert Comment

by:bruintje
ID: 8200324
0
 

Author Comment

by:cantonexpress
ID: 8206061
Folks

Have amended the code to :


Sub Email_All_Business_Managers()
Dim MyDate, MyStr
    MyDate = Date
    MyStr = Format(Date, "Short Date")
    MyStr = Format(MyDate, "dd-mm-yy")
Dim rngCell As Range
For Each rngCell In ThisWorkbook.Names
("Business_Manager_Names").RefersToRange
busmgr = rngCell.Value
EmailSubject = busmgr & " Sales Stats " & MyStr
fdir = "C:\Documents and Settings\r_cunnin\Desktop\Accounts temp\Sales
Summaries (Text Files)\Backup Of Stats Sent To Business Managers\"
Worksheets("Parsed And Sorted Sales List").Select
With ActiveSheet
.Range("C1").AutoFilter Field:=3, Criteria1:=busmgr
.Range("C1").SpecialCells(xlCellTypeVisible).EntireRow.Copy
End With
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
ChDir "C:\Documents and Settings\r_cunnin\Desktop\Accounts temp\Sales
Summaries (Text Files)\Backup Of Stats Sent To Business Managers\"
With ActiveWorkbook
.SaveAs Filename:=fdir & busmgr & MyStr
.Close SaveChanges:=False
End With
    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 EmbedObj As Object
    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
     Else
         Maildb.OPENMAIL
     End If
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.FORM = "Memo"
    MailDoc.sendto = busmgr
    MailDoc.Subject = EmailSubject
    MailDoc.Body = "Please find enclosed the sales stats for " & busmgr &
".  If you are not " & busmgr & " then please ignore this e-mail."
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    Attachment = fdir & busmgr & MyStr & ".xls"
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment,
"Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If
    MailDoc.PostedDate = Now()
    MailDoc.SEND 0, busmgr
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
Next rngCell
End Sub


This code successfully executes the first loop in the for each next statement but then throws up an 'automation error'.

BRUINTJE

The content from your link was very good.  You can probably see from my code above the kind of idea that I am trying to do - do you have any suggestions ???  I need the recipient to be dynamic - hence the use of the loop through the named range.

As always many thx to all.

I am increasing points due to urgency for completed project.

cheers

Steve / Paulo / Bruintje
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8206115
I'm not too sure about this but maybe those Dim's and some of those CreateObject's should be outside the loop. But it's just a wild guess...

Also, do you have any idea what line is throwing the error?

Paulo
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8206201
Been out all day and late again (sorry)... looks about right Paulo.  

I'd add the first part outside the loop :

   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 EmbedObj As Object
   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
    Else
        Maildb.OPENMAIL
    End If

then

Set MailDoc = Maildb.CREATEDOCUMENT
down to
MailDoc.SEND 0, busmgr

in place

and move the set xxx=nothing to after the loop.

Steve
0
 

Author Comment

by:cantonexpress
ID: 8206433
Steve,

Reworked the code to what you suggested.

Received

Runtime error '2147417848(80010108)':
Automation error
The object invoked has disconnected from its clients.

This may be to do with the fact that I am using a dial in from home connection at the moment.  I can try your amended code at work tomorrow - when directly connected to server.  Any other possibilities that spring to mind ??

Robert
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 800 total points
ID: 8231771
Did you get any luck with this?  Sorry I didn't have much time to look at this for you before.  I've just tried your code out as supplied with a test workbook setup as it appears yours is and it works fine to one of my Notes R5 clients (set as the Island location in my case so I could see the mail messages without sending in the MAIL.BOX database).

The only thing that caused me an error on XL97 was the Range("A1").Select command, all worked OK without this for me. Which line gives you the error, you didn't say as far as I can see?

Steve

Here's the code I used fine in the end:

Sub Email_All_Business_Managers()

' Setup email conenction to Notes

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 EmbedObj As Object
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
   Else
       Maildb.OPENMAIL
   End If
MsgBox Maildb.Title & " on " & Maildb.Server, , Maildb.FilePath

Dim MyDate, MyStr
   MyDate = Date
   MyStr = Format(Date, "Short Date")
   MyStr = Format(MyDate, "dd-mm-yy")
Dim rngCell As Range

For Each rngCell In ThisWorkbook.Names("Business_Manager_Names").RefersToRange
    busmgr = rngCell.Value
    EmailSubject = busmgr & " Sales Stats " & MyStr
    ' Sorry, changed your paths!
    fdir = "c:\"
    Worksheets("Parsed And Sorted Sales List").Select
   
    With ActiveSheet
        .Range("C1").AutoFilter Field:=3, Criteria1:=busmgr
        .Range("C1").SpecialCells(xlCellTypeVisible).EntireRow.Copy
    End With
   
    Workbooks.Add
    ' Removed Select A1.
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ' Sorry, changed your paths!
    ChDir "C:\"
   
    With ActiveWorkbook
        .SaveAs FileName:=fdir & busmgr & MyStr
        .Close SaveChanges:=False
    End With
       
    ' Send email
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.FORM = "Memo"
    MailDoc.sendto = busmgr
    MailDoc.Subject = EmailSubject
    MailDoc.Body = "Please find enclosed the sales stats for " & busmgr & ".  If you are not " & busmgr & " then please ignore this e-mail."
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    Attachment = fdir & busmgr & MyStr & ".xls"
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If
    MailDoc.PostedDate = Now()
    MailDoc.SEND 0, busmgr
   
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set EmbedObj = Nothing
Next rngCell

Set Maildb = Nothing
Set Session = Nothing
End Sub

0
 

Author Comment

by:cantonexpress
ID: 8247577
Thanks Steve

Will try out asap and get back to you.

Cheers
Rob
0
 
LVL 15

Expert Comment

by:dbase118
ID: 10094942
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Answered by: dragon-it
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

dbase118
EE Cleanup Volunteer
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question