asked on

Unable to send mail from Lotus notes 8.5 with excel vba

Hi Team,

I hav this piece of code which sends mails with attachments in excel 2007 using lotus notes 8, however i am unable to send mails in lotus notes 8.5 with this code, appreciate any help.

Public Sub Send_Testemail()

'Requires reference to Lotus Domino Objects (domobj.tlb) for constants such as EMBED_ATTACHMENT and FONT_HELV, etc.

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim NSession As Object
Dim NUIWorkspace As Object
Dim NMailDb As Object
Dim NDocumentTemp As Object
Dim NUIDocumentTemp As Object
Dim NUIDocument As Object
Dim NRTItemBody As Object
Dim NRTStyle As Object, NRTStyleDefault As Object
Dim NRTItemAttachment As Object, embeddedAttachment As Object
Dim Subject As String
Dim SendTo As String, CopyTo As String, BlindCopyTo As String
Dim fileAttachment As String
Dim embedCells As Range
Dim FSO As Object
Dim tempFolder As String, tempCellsJPG As String
Dim Copy_and_Paste As Boolean
Const EMBED_ATTACHMENT As Long = 1454
Dim Name1 As String

'--------- EDIT USER-DEFINED SETTINGS IN THIS SECTION ---------

Set embedCells = Sheets("Draft").Range(Cells(1, 1), Cells(ActiveCell.Row, ActiveCell.Column + 3))

SendTo = Sheets("Values").Range("B2").Value

' SendTo = "sajeev.nair@xe04.ey.com"
CopyTo = ""
BlindCopyTo = ""
Subject = "EY Training Courses"

'--------- END OF USER-DEFINED SETTINGS ---------

'Copy_and_Paste flag
'True = copy and paste Excel cells into email body using the clipboard
'False = save Excel cells as a temporary .jpg file and import into email body

Copy_and_Paste = False

Set FSO = CreateObject("Scripting.FileSystemObject")
tempFolder = FSO.GetSpecialFolder(2)

'File name for temporary .jpg file containing Excel cells

tempCellsJPG = tempFolder & "\" & Replace(FSO.GetTempName(), ".tmp", ".jpg")

Set NSession = CreateObject("Notes.NotesSession") 'OLE (late binding only) because we access Notes UI classes
Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
Set NMailDb = NSession.GetDatabase("", "")
NMailDb.OpenMail

'Create the default rich text style

Set NRTStyleDefault = NSession.CreateRichTextStyle
With NRTStyleDefault
' .NotesColor = COLOR_BLUE
' .FontSize = 8
' .NotesFont = FONT_HELV
' .Bold = False
' .Italic = False
End With

Set NRTStyle = NSession.CreateRichTextStyle

'Create a temporary NotesDocument

Set NDocumentTemp = NMailDb.CreateDocument
With NDocumentTemp
.Form = "Memo"

'Add a rich text item to contain the email body text and file attachment

Set NRTItemBody = .CreateRichTextItem("Body")
With NRTItemBody

'--------- ADD/EDIT CODE IN THIS SECTION FOR THE EMAIL BODY TEXT ---------

'Compose the email body text

' .AppendText ""
' .AddNewLine 2

With NRTStyle
'.NotesFont = FONT_ROMAN
' .FontSize = 14
' .NotesColor = COLOR_BLUE
' .Bold = True
End With
' .AppendStyle NRTStyle
' .AppendText ""
' .AddNewLine 2

'Add placeholder text which will be replaced by the Excel cells

.AppendText "{PLACEHOLDER}"
' .AddNewLine 2

With NRTStyle
' .NotesFont = FONT_HELV
' .FontSize = 10
' .NotesColor = COLOR_RED
' .Italic = True
End With
' .AppendStyle NRTStyle
' .AppendText ""

'Same paragraph, default style

' .AppendStyle NRTStyleDefault
' .AppendText " Excel cells are shown above."

fileAttachment = "C:/Training/Training Planner.pdf"

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"

.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'
End If

fileAttachment = Sheets("Values").Range("H2").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
'
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'
End If

fileAttachment = Sheets("Values").Range("H3").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
' .AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H4").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H5").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H6").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H7").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H8").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H9").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

fileAttachment = Sheets("Values").Range("H10").Value

If fileAttachment <> "" Then
' .AddNewLine 2
' .AppendText fileAttachment & " attached"
' .AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
'.AddNewLine 1
End If

'--------- END OF EMAIL BODY TEXT SECTION --------

End With

.Save False, False
End With

On Error GoTo errhandler

Set NUIDocumentTemp = NUIWorkspace.EditDocument(True, NDocumentTemp)

'Copy the rich text to the clipboard, close the window, and delete the temp doc

With NUIDocumentTemp
.gotofield "Body"
.SelectAll
.Copy
'The next 2 lines are not needed
'.Document.SaveOptions = "0" 'prevent prompt
'.Document.MailOptions = "0" 'prevent prompt
.Close 'therefore temp UI doc not saved
End With
NDocumentTemp.Remove True

'Compose the real email document

' Set NUIDocument = NUIWorkspace.ComposeDocument(NMailDb.Server, NMailDb.filePath, "Memo")
Set NUIDocument = NUIWorkspace.ComposeDocument(, , "Memo") 'use local computer and current database
With NUIDocument
.FieldSetText "EnterSendTo", SendTo
.FieldSetText "EnterCopyTo", CopyTo
.FieldSetText "BlindCopyTo", BlindCopyTo
.FieldSetText "Subject", Subject

'The memo now has everything except the rich text from the temporary UI document and the Excel cells image.
'The automatic signature (if defined in User Preferences) should be at the bottom of the memo. Now, we just
'paste the rich text and Excel cells into the body

.gotofield "Body"
.Paste

'Replace the placeholder text with the Excel cells image

.gotofield "Body"
.FindString "{PLACEHOLDER}"
' .DESELECTALL 'Uncomment to leave the placeholder text in place (cells are inserted immediately before it)

If Copy_and_Paste Then
embedCells.CopyPicture xlBitmap
.Paste
Application.CutCopyMode = False
Else
Save_Object_As_JPG embedCells, tempCellsJPG
.Import "JPEG Image", tempCellsJPG
Kill tempCellsJPG
End If

'Set NotesDocument options to save and send the email without prompts when the Close method is called

.Document.SaveOptions = "1"
.Document.MailOptions = "1"

.Close
End With

DoEvents

MailStatus

Sheets("TrainingForm").Select

Set NSession = Nothing
Set NUIWorkspace = Nothing
Set NMailDb = Nothing
Set NRTItemBody = Nothing
Set NRTStyle = Nothing
Set NRTItemAttachment = Nothing
Set embeddedAttachment = Nothing
Set NRTStyleDefault = Nothing

errhandler:
If Err.Number = 7742 Then
MsgBox "Lotus notes error"
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

DoEvents
End Sub

Private Sub Save_Object_As_JPG(saveObject As Object, JPGfileName As String)

'Save a picture of an object as a JPG/JPEG file

'Arguments
'saveObject - any object in the CopyPicture method's 'Applies To' list, for example a Range or Shape
'JPGfileName - the file name (including folder path if required) to save the picture as

Dim temporaryChart As ChartObject

Application.ScreenUpdating = False

saveObject.CopyPicture xlScreen, xlPicture

Set temporaryChart = ActiveSheet.ChartObjects.Add(0, 0, saveObject.Width, saveObject.Height)
With temporaryChart
.border.LineStyle = xlLineStyleNone 'No border
.Chart.Paste
.Chart.Export JPGfileName
.Delete
End With

Application.ScreenUpdating = True

Set temporaryChart = Nothing

End Sub

larsberntrop

Suggestion: implement rigorous logging, and report every error. Every sub or function that is not methematically proven to be absolutely bulletproof should log the errorcode, the explanation of that error, the line number, the procedure and the caller.

code example:

Sub exampleSub
	Const proc = "exampleSub" ' for customization of the error routine
	On Error GoTo errH
	
' everbody's bug ridden code goes here
	
	Exit Sub
	
errH:
	' lg is my custom logging global
	lg.LOGERROR CStr(Err) & ": " & Error$ & " occurred on line " & Erl() & " in Procedure: " & proc & " called by: " & GetThreadInfo(10)
	' GetThreadInfo(10) is a Lotus function returning the caller of this procedure
	' if possible implement this in your environment too, since it is often handy to know who called the faulting routine
	Exit Sub
End Sub

Open in new window

on this specific problem: You mention it worked on 8 and fails on 8.5. How was the version change effected?

sajeevn

ASKER

i am using excel 2007 with lotus notes 8. , Recently my team upgraded to excel 2010 and lotus notes 8.5 and they are not able to send mails using the code

larsberntrop

without error logging, how would you know what causes the error?
You changed two sides of the equation, and post code without any error messages. We (speaking for all experts) cannot help you without more specific information.

Sjef Bosman

@Lars: like you, I'm not going to read and try to understand 200 lines of code while filtering all the line commented out...

A suggestion (if you don't want to introduce the error messages asked for by Lars): copy the agent or action to a new one, just for testing purposes, and reduce the code of the agent or action to a minimum. Then debug the code.

sajeevn

ASKER

I get the runtime error - 77412

(Notes Error - specified command not available from the workspace)

Please suggest a remedy to resolve the error

Sjef Bosman

For suggestions, see above.

Where does the execution of your code stop? On the ComposeDocument maybe? Or on any of the EmbedObject calls?

sajeevn

ASKER

the execution of the code happens On the ComposeDocument

Sjef Bosman

Any particular reason why you removed server and filename from the ComposeDocument call? Does it work when you comment that line out and uncomment the line immediately above?

sajeevn

ASKER

it doesnt work if i uncomment the line above as i dont know the details to be entered there,
it works fine with the below statement but sometimes it does throw the error 7412

Set NUIDocument = NUIWorkspace.ComposeDocument(, , "Memo") 'use local computer and current database

Sjef Bosman

Sometimes? Please explain. What are the differences when it works and when it doesn't?

I don't understand... At what line does execution stop??

Error 7412 is cannot locate field. Strange... Exactly which version of Notes do you use? R8.5 or R8.5.3 or so?

ASKER CERTIFIED SOLUTION

larsberntrop

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sjef Bosman

It is the "sometimes" that bugs me...

larsberntrop

Perhaps is dependent on Notes started or not?

sajeevn

ASKER

Thank you all for the help, its working fine now

@larsberntrop: Appreciate ur support big time ...

Sjef Bosman

You're welcome...