Thread: Changing VBA code from Outlook to Lotus Notes

Hi All,

I would appreciate some expertise on this issue.

I have the following code from Ron De Bruin's website (http://www.rondebruin.nl/mail/folder3/message.htm) for emailing a small message from a range.

Sub TestFile()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
 
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
    On Error GoTo cleanup
    For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
            Set OutMail = OutApp.CreateItem(0)
 
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
                        "Please contact us to discuss bringing your account up to date"
                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display
            End With
            On Error GoTo 0

            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

I'm ok editing the code to fit in with the spreadsheet I wish to apply it to but the problem I have is that our company uses Lotus Notes (v7) and I have no idea how to amend the code to work with Notes.

Any help anyone can offer would be greatly appreciated.

Tom

Good morning tommytee

You're better off starting from scratch to be honest. This code should get you started to send a straight forward e-mail - just amend it to suit. I don't have access to Notes 7, but have tested it on Notes 6.5. I would be interested in confirmation that it works OK on the latest version.

Sub EmailUsingNotes()
Dim oSess As Object, oDB As Object, oDoc As Object
Dim oItem As Object, direct As Object
Dim Var As Variant, flag As Boolean
Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")
If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler
'Building the message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.Subject = "" 'Put the subject in here
oDoc.sendto = "" 'Put the e-mail address in here
oDoc.Body = "" 'Put the body of your e-mail in here
oDoc.postdate = Date
oDoc.SaveMessageOnSend = True
'Setting up attachments
Call oItem.EmbedObject(1454, "", "C:\MyAttachment.xls") 'Put the full path to any attachments here
oDoc.visable = True
oDoc.Send False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment
End Sub

HTH

DominicB

Hi Dominic,

Thanks for your prompt reply. Let me firstly apologise as I am a novice in this area at the moment but I'm not sure this is exactly what I need. (I'm apologising because I could be wrong!)

The basic situation is thus:

I will have a list of contacts (A:A) with email addresses (B:B) and the idea was to have check box linked to cells in C:C and then based on the 'True' or 'False' value in cell C, open a separate message for each inidividual.

This will be partly a manual process, i.e. an administrator will decide who needs to receive an email based on other information within the spreadsheet and check the boxes accordingly, then run the code to execute this.

I hope this makes sense and thanks again

Tom

Hi tommytee

Firstly, will you check that the code above works - do this by finding the cells where comments are added and add a body, a header, and an addressee and just run the code to make sure that this code works OK on Notes 7.

Then let me know where the body of the e-mail will be, and where the header will be, perhaps providing a cut down version of what you have at the moment. Also, I'm guessing that the "contact" in column A is to be the salutation (eg Dear tommytee etc)?

HTH

DominicB

Hi Dominic,

Exciting stuff! The code works fine in Notes 7. Really good stuff thanks

The only thing I would say is that there will definitely be no attachment and I would probably like to open the emails to be checked rather than immediately sent (altho that is very cool).

The body text would be something along the lines of;

Dear 'Salutation'

Your Contract is due to expire on 'Date'

Please let me know if you would like to renew to 'Date2' as soon as possible..

With my limited knowledge, I imagine the text that will remain constant can be contained within the code and the specifics in bold kept on the spreadsheet and pulled through from there?

I've attached up a basic template of what kind of data fields will be there. You will see that there is a manager email too. I will also need the option to email these guys as well with slightly different text in the email body.

I hope this makes sense

Cheers

Tom

Thanks Roy,

My intention was to link the check box to each cell to return a TRUE/FALSE and reference that in the code, but I'm not sure if thats the best way.

Tom

Hi Roy,

Thanks for that code - top notch!

I'll definitely use that

Tom

Hi tommytee

Roy's suggestion would be easier than to create 500 tick boxes : that said the example I have used utilises a tick box, as I have only one row to cater for. If you want to go for Roy's example, no great changes will be required.

To kick the thing off in your example you will need to run the macro "BuildMessage" which will loop through all rows and send an e-mail based on the information in the sheet where column N says True.

I am looking into saving the resultiung e-mail into drafts, but this is not quite as simple as it would be in Outlook. Unsurprisingly, Notes doesn't integrate into Office quite as well as Microsoft products do - in fact it is the command

oDoc.postdate = Now()

which places the mail into the sent folder, so it's not quite as straightforward as one might think.

HTH

DominicB

Originally Posted by royUK

Hi Dominic, does Notes not have a .Show command or similar?

Thanks for the thought Roy, but unfortunately, but the .Show command (and is there also a .Display command?) are unsupported, however I've found a .Save instruction that seems to work with a slight syntax tweak, so TommyTee in the macro replace this line :

oDoc.Send False

with this :

Call oDoc.Save(True, False)

to get the unsent mail to go to your Drafts folder for review.

HTH

DominicB

That's amazing.

I've tweaked a bit of the text but its really starting to look like what I wanted so many thanks for your helps guys.

I was just trying to use Roy's code for double clicking but can't seem to get it to work in the test sheet. I wanted to change it to column A aswell but couldn't see how to do it.

Cheers

Tom

Hi tommytee

Originally Posted by tommytee

I was just trying to use Roy's code for double clicking but can't seem to get it to work in the test sheet. I wanted to change it to column A aswell but couldn't see how to do it.

Not quite sure what you mean, but if you're really struggling feel free to upload a small sample of where you're up to and someone will try and help you out.

DominicB

Ok,

This is the original code from Roy

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'limit action to C
If Target.Column <> 3 Then Exit Sub
If IsEmpty(Target) Then
With Target
.Value = "a"
.Font.Name = "Marlett"
End With
Else: Target.ClearContents
End If
End Sub

All I did was paste it into the test spreadsheet module below everything else but I couldn't work out how it actually works! I couldn't see how it was limited to column C (as the 'limit action to C' was just a label I think)

Also do I need to use a separate module?

Thanks

Tom