Sort-of-sending an Excel workbook from Lotus Notes

Having Just done this myself I modified a bit of code I was pointed at by
Ivan from this thread

http://www.mrexcel.com/board2/viewtopic.php?p=1236&highlight=lotus+notes#1236

I'm hoping the html PRE tags will work here so here's the code I came up with and comments.

CODE STARTS BELOW:


'******************************************************************************************

Sub Lotus_notes_EMail_Return()

'

'**** On Screen Warning****

ActiveWorkbook.Save
Application.ScreenUpdating = False
Application.DisplayAlerts = False

yesno = MsgBox(" This will return the completed form to the Resource desk." _
& vbCrLf & " You may delete the E-Mail After. " _
& vbCrLf & " Do you wish to send the Report?", vbYesNo + vbInformation, "Report Generation.")





Select Case yesno
Case vbNo
Exit Sub
End Select
Select Case yesno
Case vbYes






'****Declare Variables for file and macro setup****

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

'****Open and locate current LOTUS NOTES User****

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

'**** Create New Mail and Address / Title Handlers*****


Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
Recipient = "Target@TARGET.COM" 'Where you want it to go
MailDoc.sendto = Recipient

ccRecipient = Range("h21:h23").Value 'Select a range of cells to contain E-Mail
'addresses for CC Copies.
MailDoc.CopyTo = ccRecipient

Subject = "IOT Team Return " & Range("d2") ' E-Mail Subject
MailDoc.Subject = Subject
BodyText = "IOT Data Return for " & Range("d2") ' Body Text
MailDoc.Body = BodyText

'**** Create and Name Temp Workbook****





Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"c:\temp\IOT Team Figures.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


ThisWorkbook.Worksheets("Sheet1").Range("a1:f30").Copy
Destination = ActiveWorkbook.Worksheets("Sheet1").Range("A1:f30")
ActiveSheet.Paste 'Select Area to copy. You could delete the.Range("A:B")
'To select the whole sheet.

Range("D4:E4").Select
Selection.Copy
Range("D4:E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' Second Range to be copied if required


Application.CutCopyMode = False




'**** Select Workbook to Attach to E-Mail****


MailDoc.savemessageonsend = True
attachment1 = "c:\temp\IOT Team Figures.xls" 'Required File Name


ActiveWorkbook.SaveAs ("C:\Temp\IOT Team Figures " & Range("D2") & ".xls")
' How you want the file saved in the Temp Directory



If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\Temp\IOT Team Figures " & Range("D2") & ".xls", "") 'Use the Temp file name here


On Error Resume Next
End If
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.SEND 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing

'**** Routine to Generate a copy if required****

OnOff = MsgBox("Do you want to save a copy?", vbYesNo + vbInformation, "Save Copy?")

Select Case OnOff
Case vbNo
ActiveWorkbook.Close
Exit Sub
End Select
Select Case OnOff

Case vbYes
Set NewBook = ActiveWorkbook
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName
ActiveWorkbook.Close

End Select
Exit Sub



errorhandler1:

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Select




End Sub


END CODE:

I use this attaced to a button in worksheets I send out to return them to me,
rather than the person having to launch the attachment, save a copy and set up a new E-Mail and attach the saved copy, then sending it back to me.


If anyone has any comments on the code please let me know. This is about the biggest thing I've done so far.

I know most of it was plagerised but I was pleased I managed to make the changes and adapt the existing code.

Hope this helps.

DaveA

Thank for the nice PM Juliea!

I'll answer the question here rather than PM back tio see if anyone else needs it!.

The code I have there takes a section of a workbok the macro's in.

The bit of code you want to invoke (Isn't that a wonderful word ) is...

****Declare Variables for file and macro setup****

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

'****Open and locate current LOTUS NOTES User****

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

This locates the current Notes user.

The following bit attaches the sheet.

'**** Select Workbook to Attach to E-Mail****


MailDoc.savemessageonsend = True
attachment1 = "c:\temp\IOT Team Figures.xls" 'Required File Name


ActiveWorkbook.SaveAs ("C:\Temp\IOT Team Figures " & Range("D2") & ".xls")
' How you want the file saved in the Temp Directory



If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\Temp\IOT Team Figures " & Range("D2") & ".xls", "") 'Use the Temp file name here

The bit in Bold is the crucial code.


What you can do is instead of creating a new sheet like I have here is.
Run the macro from another workbook etc. Then put in an input box asking which file you want to attach.

The TARGET@TARGET.COM instead of being hard coded can also be from an input box as can titles CC and BCC copies.

Hope this helps a bit.

DaveA

Re: Sort-of-sending an Excel workbook from LotusNotes UPDATE

Just as an aside folks.

Can anyone point me in the right direction for how I'd start to develop this into an addin?

I'm still disappointed that you can't use the Mail To function and would like
to try.

It's a little out of my depth so any pointers welcome (and any coments on the existing code structure)

Thanks

DaveA