Closing an Edited Lotus Notes Email Using VBA

[jsugar]

Hey there,

I created an Excel-based reporting system, which uses individual files in a shared folder. The users enter their updates in their files, and then use macros in the file to

1. Request approval for their updates from their managers, and
2. Release the approved data for consolidation.

The issue I am encountering has to do with item 1. The macro to request approval opens up a Lotus Notes e-mail, populates it with the appropriate contacts, subject, and body, and then copies the relevant cells in Excel and pastes them as a picture into the Lotus Notes email (allowing the approver to view the update on their computer or BlackBerry).

The macro has worked very well in the past, but we've had one periodically reoccurring issue. From time to time when the macro is run (generally the first time the macro has been used in a while), the email will generate appropriately but the picture of the excel cells will not appear. When this happens if the user closes the email, and reruns the macro everything works properly.

We've requested that our users use this work around, but ideally I'd like to fix this in a better way. From my experience, the code to copy and paste the Excel cells in Lotus Notes always works the second time, so what I want to do is create code that would

1. create the email like it usually does
2. close this first e-mail
3. recreate the email

So far I have been able to open and close a blank email, but I have not been able to close an email that has been edited (adding in the recipient names, subject, body, etc.). When I try to close the edited email, Lotus Notes opens a window asking "Do you want to send, save, or discard your changes? Choose Cancel to continue editing". I tried using SendKeys to send "D" for discard, but I just get a little note saying that Numlock has turned off.

Anyone have any suggestions?
I'm including my code below. You can see where the email code runs, and then where I try to close the email in red. Special thanks to NateO for putting together the code that I worked from.

[INDENT=2]Sub EmailforApproval()[/INDENT]
[INDENT=2]'Opens approval email and copies and pastes relevant data into e-mail without sending[/INDENT]
[INDENT=2]Application.ScreenUpdating = False[/INDENT]
[INDENT=2]ActiveWorkbook.Save[/INDENT]
[INDENT=2]Sheets("Reference").Visible = True[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Dim UserName As String, MailDbName As String, ccRecipient As String, attachment1 As String, sendRecipient As String[/INDENT]
[INDENT=2]Dim Maildb As Object, MailDoc As Object, AttachME As Object, Session As Object[/INDENT]
[INDENT=2]Dim EmbedObj1 As Object, workspace As Object, NUIdoc As Object, NUIWorkSpace As Object[/INDENT]
[INDENT=2]With Application[/INDENT]
[INDENT=2].ScreenUpdating = False[/INDENT]
[INDENT=2].DisplayAlerts = False[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Set Session = CreateObject("Notes.NotesSession")[/INDENT]
[INDENT=2]UserName = Session.UserName[/INDENT]
[INDENT=2]MailDbName = _[/INDENT]
[INDENT=2]Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"[/INDENT]
[INDENT=2]Set Maildb = Session.GETDATABASE("", MailDbName)[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]If Maildb.IsOpen = True Then[/INDENT]
[INDENT=2]Else[/INDENT]
[INDENT=2]Maildb.OPENMAIL[/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Set MailDoc = Maildb.CreateDocument[/INDENT]
[INDENT=2]MailDoc.Form = "Memo"[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]sendRecipient = Sheets("Reference").Range("D6").Value[/INDENT]
[INDENT=2]ccRecipient = Sheets("Reference").Range("D7").Value[/INDENT]
[INDENT=2]MailDoc.Sendto = sendRecipient[/INDENT]
[INDENT=2]MailDoc.CopyTo = ccRecipient[/INDENT]
[INDENT=2]MailDoc.Subject = Sheets("Reference").Range("d8").Value[/INDENT]
[INDENT=2]MailDoc.body = vbNewLine & vbNewLine & "This data is ready for you to approve" & vbNewLine & vbNewLine & _[/INDENT]
[INDENT=2]            "Please press 'reply all' and indicate whether or not you approve these figures. Thank you"[/INDENT]
[INDENT=2]MailDoc.Save True, False[/INDENT]
[INDENT=2]        Set NUIdoc = NUIWorkSpace.EDITDocument(True, MailDoc)[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]    With NUIdoc[/INDENT]
[INDENT=2]        .GotoField ("Body")[/INDENT]
[INDENT=2]        Sheets("Input Sheet").Range("b4:l5").Copy[/INDENT]
[INDENT=2]        .Paste[/INDENT]
[INDENT=2]        Application.CutCopyMode = False[/INDENT]
[INDENT=2]    End With[/INDENT]
[INDENT=2][COLOR=#FF0000]    With NUIdoc[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]    .Close[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]    End With[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]'    AppActivate "Lotus Notes"[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]    SendKeys "d", True[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]    'SendKeys "[TAB]", True[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]    'SendKeys "[TAB]", True[/COLOR][/INDENT]
[INDENT=2][COLOR=#FF0000]    'SendKeys "[Enter]", True[/COLOR][/INDENT]
[INDENT=2]    Set NUIdoc = NUIWorkSpace.EDITDocument(True, MailDoc)[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]    With NUIdoc[/INDENT]
[INDENT=2]        .GotoField ("Body")[/INDENT]
[INDENT=2]        Sheets("Input Sheet").Range("b4:L5").Copy[/INDENT]
[INDENT=2]        .Paste[/INDENT]
[INDENT=2]        Application.CutCopyMode = False[/INDENT]
[INDENT=2]    End With[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]MailDoc.SaveMessageOnSend = True[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Set Maildb = Nothing[/INDENT]
[INDENT=2]Set MailDoc = Nothing[/INDENT]
[INDENT=2]Set AttachME = Nothing[/INDENT]
[INDENT=2]Set Session = Nothing[/INDENT]
[INDENT=2]Set EmbedObj1 = Nothing[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2].ScreenUpdating = True[/INDENT]
[INDENT=2].DisplayAlerts = True[/INDENT]
[INDENT=2]End With[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Sheets("Reference").Visible = False[/INDENT]
[INDENT=2]Application.ScreenUpdating = True[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]AppActivate ("Notes")[/INDENT]
[INDENT=2]errorhandler1:[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Set Maildb = Nothing[/INDENT]
[INDENT=2]Set MailDoc = Nothing[/INDENT]
[INDENT=2]Set AttachME = Nothing[/INDENT]
[INDENT=2]Set Session = Nothing[/INDENT]
[INDENT=2]Set EmbedObj1 = Nothing[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]End Sub[/INDENT]

JSugar