We have recently migrated fro Exchange and Outlook to the wonder of Lotus Notes. Whilst Lotus Notes is impressive - I now have to redo all of the macros which worked with Excel and Outlook and I’m completely stuck.
Ideally what I need the macro to do is, at the push of a button is copy either one or two worksheets from Excel, then attach them to an email as a new renamed document, ready to be sent.
The code below is what I used for the Outlook method, but I have no idea whatsoever what I need to do to make this work in Lotus Notes.
Sub Mail_SheetsArray_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("Project Record Sheet", "Tapered U Value")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs (Range("E6") & (" ") & Range("E8") & (".xls"))
'.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = Range("E6") & " " & Range("E8")
.Body = "Should you have any enquiries, please do not hesitate to contact me"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or .Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
In the VB editor in Excel, provided that you are working on a PC that has both Notes and Excel installed, you can go to the Tools -References menu and you should see Lotus Domino objects listed. Tick the check box.
Now you can code Lotus Notes objects and the type ahead help facility will work. All you need to do now is learn the object model for Lotus Notes to get it to do what you want.
Check out the Lotus Designer help file COM section for examples: e.g. to initialise a session
Dim s As New NotesSession
Call s.Initialize
Then just work out how to build and send an e-mail. Basically create a NotesDocument object, set a field called ‘Form’ to be ‘Memo’ , add SendTo, CopyTo, BlindCopyTo, Subject, Body (which must be rich text item). Attach your file and call the NotesDocument.send method. If you search the R5 and R6/7 forum for examples of the LotusScript you should basically be able to copy to code straight into Excel. Foreach loops in VB are replaced by Forall in LotusScript, but otherwise it’s pretty much the same.
When you say this is easy to do - say for example I was a fool and had no idea where to start - how would you explain it to me, without me getting an inferiority complex
Well I would use smaller words for a start and speak SLOWLY
However, if you have absolutely no idea about writing in script then you could find it a little challenging. My advice is to find a working example and copy
When COM was first included in Note (5.0.5b??), Gary Devendorff (who used to work for Lotus) wrote a series of examples. These were in LotusSphre, Florida one year and are also available on the web. He has a new site now.
