Closing an Edited Lotus Notes Email Using VBA

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. Special thanks to NateO for putting together the code that I worked from.

Sub EmailforApproval()
'Opens approval email and copies and pastes relevant data into e-mail without sending
Application.ScreenUpdating = False
ActiveWorkbook.Save
Sheets("Reference").Visible = True


Dim UserName As String, MailDbName As String, ccRecipient As String, attachment1 As String, sendRecipient As String
Dim Maildb As Object, MailDoc As Object, AttachME As Object, Session As Object
Dim EmbedObj1 As Object, workspace As Object, NUIdoc As Object, NUIWorkSpace As Object
With Application
.ScreenUpdating = False
.DisplayAlerts = False


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)

Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")

If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If


Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"

sendRecipient = Sheets("Reference").Range("D6").Value
ccRecipient = Sheets("Reference").Range("D7").Value
MailDoc.Sendto = sendRecipient
MailDoc.CopyTo = ccRecipient
MailDoc.Subject = Sheets("Reference").Range("d8").Value
MailDoc.body = vbNewLine & vbNewLine & "This data is ready for you to approve" & vbNewLine & vbNewLine & _
            "Please press 'reply all' and indicate whether or not you approve these figures. Thank you"
MailDoc.Save True, False
   
        Set NUIdoc = NUIWorkSpace.EDITDocument(True, MailDoc)

    With NUIdoc
             
        .GotoField ("Body")
            
        Sheets("Input Sheet").Range("b4:l5").Copy
        .Paste
        
        Application.CutCopyMode = False
    End With
    
    With NUIdoc
    .Close
    End With
'    AppActivate "Lotus Notes"
    SendKeys "d", True
    'SendKeys "[TAB]", True
    'SendKeys "[TAB]", True
    'SendKeys "[Enter]", True
    
    
    Set NUIdoc = NUIWorkSpace.EDITDocument(True, MailDoc)

    With NUIdoc
        
        .GotoField ("Body")
        
        Sheets("Input Sheet").Range("b4:L5").Copy
        .Paste
        Application.CutCopyMode = False
    End With

MailDoc.SaveMessageOnSend = True

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

.ScreenUpdating = True
.DisplayAlerts = True
End With

Sheets("Reference").Visible = False
Application.ScreenUpdating = True

AppActivate ("Notes")
errorhandler1:

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

End Sub

Thank you for your time and assistance

JSugar

Thank you so much for your response! Yes, I am still trying to find a solution to this problem. I will take a look at what you have provided and give it a shot. I'll follow up tomorrow after I have had a chance to do some testing. Thank you again for your time and assistance!

Thanks again, Chippy! I think the delay is a great solution so I am going to go ahead and implement it for our next reporting cycle. I'll follow up if I see the issue reappear after I implement this fix. Thanks again!!