VBA Code: Lotus Notes Email Preparation From Excel Worksheet

[Flani]

Hi All,


I managed to get a code that will automate my email preparation. The code gets recipients, subject and the body of the mail from the active worksheet and it works perfectly for the recipients and subject, not so much for the body.


Say the mail body is c1, c2, c3, c4, c5, c6, c7 and c8


C1 is a blank cell.


C2= Hi All,
(C1 here for space)
C3= Please open the file in:
(C1 here for space)
C4= is the filepath plus a formula that updates the date of the file. The font should be in blue
(C1 here for space)
C5: is another body of the mail that should be italicized and in color red
(C1 here for space)
A screenshot "mypic" should be added in this line.
(C1 here for space)
C6=another part of the mail that should be in bold letters and color red
(C1 here for space)
C7=another part of the body of the mail



The code only finishes up to C5 and adds the attachment but without the additional formatting. The other part of the body cannot be captured bythe code anymore after the attachment has been pasted. Can you help me with a code please. Thanks

 

[John_w]

... I still get the same error in the same place, or when I try to run this line:

Set NUIDocument = NUIWorkspace.COMPOSEDOCUMENT(NMailDb.Server, NMailDb.FilePath, "Memo")

So I added the code:

Application.Wait DateAdd("s", 2, Now)

You shouldn't need the Wait there, though it shouldn't do any harm.

As already shown in the code, try the alternative ComposeDocument line:

    Set NUIDocument = NUIWorkspace.ComposeDocument(, , "Memo")      'use local computer and current database

You could also try AppActivate "Actual Lotus Notes window title" before the EditDocument.

Sorry, but I can't really help much more because I don't have LN 8.5.

 

[Arithos]

This is a wierd problem :S

Lotus Notes needs to be maximized for the macro to work, I have tried a work-around, but cant seem to find one.

Is there a way to make sure Noes is maximzed?

some sort of code like this.

if application "Lotus Notes" = Minimized then
***Maximize it***
end if

That would solve it, I've searched the www, people have the same problem, but did not find a solution =(

 

[John_w]

Lotus Notes needs to be maximized for the macro to work

Put this code in a separate module:

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias _
    "GetWindowTextLengthA" (ByVal hWnd As Long) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal wCmd As Long) As Long

Private Declare Function ShowWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Private Const GW_HWNDNEXT = 2
Private Const SW_SHOWNORMAL = 1
Private Const SW_SHOWMINIMISED = 2
Private Const SW_SHOWMAXIMISED = 3


Public Sub Show_Lotus_Notes_Window(matchingCaption As String)

    Dim hWnd As Long
    
    'Get window handle of the first Notes window whose caption matches the matchingCaption string, which
    'can contain wildcards; see the help on the VBA Like operator
    
    hWnd = FindNotesWindowMatchingCaption(matchingCaption)
    
    If hWnd > 0 Then
        ShowWindow hWnd, SW_SHOWNORMAL
    End If
    
End Sub

Private Function FindNotesWindowMatchingCaption(ByVal matchingCaption As String) As Long
    
    Dim hWnd As Long
    Dim windowCaption As String
    
    hWnd = FindWindow("SWT_Window0", 0)
    If hWnd = 0 Then hWnd = FindWindow("NOTES", 0)
    Do While hWnd <> 0
        windowCaption = String(GetWindowTextLength(hWnd) + 1, Chr$(0))
        GetWindowText hWnd, windowCaption, Len(windowCaption)
        windowCaption = Left$(windowCaption, Len(windowCaption) - 1)
        If windowCaption Like matchingCaption Then Exit Do
        hWnd = GetWindow(hWnd, GW_HWNDNEXT)
    Loop
    FindNotesWindowMatchingCaption = hWnd

End Function

and restore the Notes window by adding near the top of Send_Notes_Email:

    Show_Lotus_Notes_Window "*Lotus Notes*"

You can also maximise the window by changing ShowWindow hWnd, SW_SHOWNORMAL to ShowWindow hWnd, SW_SHOWMAXIMISED.

 

[Arithos]

This looks promising.

I will check this out when I get back to work!

Thx alot

 

[Arithos]

And I can now confirm that this works! <3 tank you for all your help! =)

 

[lxfar]

Hi John_W,

Thanks for the great code.

Adapted it and have it running.

The problem is I need to do the same but from a generic mailbox in a notes server.

I tried updating the code with the server name and db filename but, it does'nt work.

Can you help me ?

regards,
Luis

 

[John_w]

The problem is I need to do the same but from a generic mailbox in a notes server.

I tried updating the code with the server name and db filename but, it does'nt work.

What have you changed? Post your changed code.

Try changing:

    Set NMailDb = NSession.GetDatabase("", "")
    NMailDb.OpenMail

to specify the server and mail file strings to GetDatabase. For example:

    With NSession
        Set NMailDb = .GetDatabase(.GetEnvironmentString("MailServer", True), .GetEnvironmentString("MailFile", True))
    End With
    If Not NMailDb.IsOpen Then NMailDb.Open
    'or
    If Not NMailDb.IsOpen Then NMailDb.OpenMail

 

[Arithos]

I have another question for you John.

Is there a way to choose the "Inbox" within Lotus notes via a VBA code?

 

[John_w]

Is there a way to choose the "Inbox" within Lotus notes via a VBA code?

I don't know. Maybe there is something in the NotesUIWorkspace or NotesUIView classes you could use. See IBM Knowledge Center

 

[brajesh2020]

This is by far the most perfect code i have found related to sending a lotus notes email with attachment.

only 2 questions.
if i have to save the mail as a draft and not sent it, what to change.
if i have a sheet with 2-3 charts and copying them directly to the mail body, how to do so.