Need help in writing VBA Script RRS feed

  • Question

  • Dear All,

    I am completely new to VB scripting.  I have an excel in which I have the list of all the projects are mentioned in Row 1 and their respective contact person in Row 2.  I need a script through which I can send automated email through lotus notes individually for each project to the project SPOC.

    Please help me with a script for this.



    Thursday, August 11, 2016 9:04 AM

All replies

  • I had tried using the below script, but getting error - Object required.  Can any help me out with the error.

    Sub Send_mail()
        Dim oSession        ' AS NotesSession
        Dim strServer       ' AS MailServer
        Dim strUserName     ' AS UserName
        Dim strMailDbName   ' AS MailDbName
        Dim oCurrentMailDb  ' as NOTESDATABASE
        Dim oMailDoc        ' as NOTESDOCUMENT
        Dim ortItem         ' as NOTESRICHTEXTITEM
        Dim ortAttacment    ' as NOTESRICHTEXTITEM
        Dim oEmbedObject    '
        Dim cstrAttachment  ' as Attachment
        Dim blAttachment    '
        cstrAttachment = "c:\tivepinst.log"
        blAttachment = True
        ' Start a session to notes
        wscript.echo "## Connecting to Lotus Notes session..."
        Set oSession = CreateObject("Notes.NotesSession")
        wscript.echo ("NotesVersion     :  9.0.0" & oSession.NotesVersion)
        wscript.echo ("NotesBuildVersion: " & oSession.NotesBuildVersion)
        wscript.echo ("UserName         : 777108" & oSession.UserName)
        wscript.echo ("EffectiveUserName: 777108" & oSession.EffectiveUserName)
        wscript.echo "## GetEnvironmentString..."
        strServer = oSession.GetEnvironmentString("MailServer", True)
        wscript.echo ("Server CN=Jecintha Arulselvam,OU=MUM,O=TCS          :" & strServer)
            strUserName = oSession.UserName
        strMailDbName = Left(strUserName, 1) & Right(strUserName, (Len(strUserName) - InStr(1, strUserName, ""))) & ".nsf"
        wscript.echo ("MailDbName        :" & strMailDbName)
        wscript.echo "## Getting current Notes database..."
        ' open the mail database in Notes
        Set oCurrentMailDb = oSession.CurrentDatabase
        wscript.echo ("fileName:" & oCurrentMailDb.Filename)
        wscript.echo ("filePath:" & oCurrentMailDb.filePath)
        wscript.echo ("server:" & oCurrentMailDb.server)
        wscript.echo ("Title:" & oCurrentMailDb.Title)
        If oCurrentMailDb.IsOpen = True Then
            ' Already open for mail
            wscript.echo "## Lotus Notes mail database is already open !"
            wscript.echo "## Opening Lotus Notes mail database..."
        End If
        ' Create a document in the back end
        Set oMailDoc = oCurrentMailDb.CREATEDOCUMENT
        ' Set the form name to memo
        oMailDoc.form = "Memo"
        With oMailDoc
            .SendTo = ""
            .BlindCopyTo = ""
            .CopyTo = ""
            .Subject = "This is a test of VB scripting driving Lotus Notes 7 "
        End With
        Set ortItem = oMailDoc.CREATERICHTEXTITEM("Hi,")
        With ortItem
            .AppendText ("Please share the revenue projection for your respective accounts")
            .AddNewLine (2)
            .AppendText ("Regards, Jecintha")
        End With
        ' Create additional Rich Text item and attach it
        If blAttachment Then
            Set ortAttacment = oMailDoc.CREATERICHTEXTITEM("Attachment")
            ' Function EMBEDOBJECT(ByVal TYPE As Short, ByVal CLASS As String, ByVal SOURCE As String, Optional ByVal OBJECTNAME As Object = Nothing) As Object
            ' Member of lotus.NOTESRICHTEXTITEM
            Set oEmbedObject = ortAttacment.EMBEDOBJECT(1454, "c:\tivepinst.log", cstrAttachment, "Attachment")
        End If
        wscript.echo "## Sending email..."
        With oMailDoc
            .PostedDate = Now()
            .SAVEMESSAGEONSEND = "True"
            .Send (False)
        End With
        wscript.echo "## Sent !"
        ' close objects
        Set oMailDoc = Nothing
        Set oCurrentMailDb = Nothing
        Set oSession = Nothing
     End Sub

    Friday, August 12, 2016 4:33 AM
  • I don't know anything about Lotus Notes, and have no means of testing your code, but did you set a reference to Lotus Notes in VBA Tools > References? You should then be able to remove the comment apostrophes from your DIM statements, which might take you a bit farther and help identify the error.

    Graham Mayor - Word MVP

    Friday, August 12, 2016 4:50 AM
  • I am getting -  Compile error: User-defined type not defined after removing the apostrophes.  In the references, I have the below references of Lotus notes added:

    1. LotNotesUI ActiveX Control Module
    2. Lotus Domino Objects

    Please let me know if I need to add any new references.

    Friday, August 12, 2016 6:25 AM
  • As I said, I don't know anything about Lotus Notes, but Google knows a man who does and looking at might provide some clues - notably that the writer considers there might be a bug in the Lotus Notes API that requires late binding to the object, which suggests that all the dim statements that are not strings should be declared as Objects and those that are strings should be declared as strings. The references would then be superfluous.

    You may even be able to adapt the code in the link to your requirements

    Graham Mayor - Word MVP

    Friday, August 12, 2016 7:35 AM
  • Hi,

    I have managed to get the below code which is working fine.  However, I am able to send in mail only the active sheet as the attachment and not the complete worksheet.  I have three sheets in the workbook.  Can some one help me attach the complete work book and not just the active sheet in the attachment?


    Const stSubject As String = "Open Requirements for Q2'17"

    Const vaMsg As Variant = "Dear All," & vbCrLf & _
                             "Kindly share the Open requirements for Q2'17 at the earliest," & vbCrLf & _
                             "Kind regards," & vbCrLf & _

    Const vaCopyTo As Variant = ""

    Sub Send_Active_Sheet()
      Dim stFileName As String
      Dim vaRecipients As Variant
      Dim noSession As Object
      Dim noDatabase As Object
      Dim noDocument As Object
      Dim noEmbedObject As Object
      Dim noAttachment As Object
      Dim stAttachment As String
      'Copy the active sheet to a new temporarily workbook.
      With ActiveSheet
        stFileName = .Range("A1").Value
      End With
      stAttachment = stPath & "\" & stFileName & ".xls"
      'Save and close the temporarily workbook.
      With ActiveWorkbook
        .SaveAs stAttachment
      End With
      'Create the list of recipients.
      vaRecipients = VBA.Array("", "")
      'Instantiate the Lotus Notes COM's Objects.
      Set noSession = CreateObject("Notes.NotesSession")
      Set noDatabase = noSession.GetDatabase("", "")
      'If Lotus Notes is not open then open the mail-part of it.
      If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
      'Create the e-mail and the attachment.
      Set noDocument = noDatabase.CreateDocument
      Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
      Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
      'Add values to the created e-mail main properties.
      With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
      End With
      'Delete the temporarily workbook.
      Kill stAttachment
      'Release objects from memory.
      Set noEmbedObject = Nothing
      Set noAttachment = Nothing
      Set noDocument = Nothing
      Set noDatabase = Nothing
      Set noSession = Nothing
      MsgBox "The e-mail has successfully been created and distributed", vbInformation
    End Sub

    Wednesday, August 17, 2016 6:40 AM
  • Just remove the .copy statement
    With ActiveSheet
        stFileName = .Range("A1").Value
      End With
      stAttachment = stpath & "\" & stFileName & ".xls"
      With ActiveWorkbook
        .SaveAs stAttachment
      End With

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 17, 2016 7:58 AM
  • I tried removing the .copy statement.  But am getting the below error:

    Microsoft Visual Basic for Applications
    System Error &H80010105 (-2147417851).   The server threw an exception. 
    OK   Help   

    Please help

    Wednesday, August 17, 2016 8:41 AM
  • Pls check what is the value of...




    And pls check if a file with same name is already existing there.

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 17, 2016 9:03 AM
  • Please ignore my previous comment.  When I tried to remove the .copy statement, i can see a file getting created in the located for the attachment and the macro sheet getting closed without any progress.
    Wednesday, August 17, 2016 9:56 AM
  • In that case you are running the macro from attachment file itself.Pls do below..

    Open the file where the above programme available...Then open the file which you need to attach...Then run the macro.....

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 17, 2016 10:41 AM
  • It made no difference.  I am still getting mail with only the active sheet.
    Thursday, August 18, 2016 6:27 AM
  • Activesheet.Copy method copies activesheet based on [Before] and [After] parameter. If omitted then a new workbook is created with activesheet.

    In above code same thing happened. It seemed most likely to be problem spot.

    I searched a bit and found that below link may be of help.

    Ron de Bruin Excel Automation

    See if it helps.

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, August 18, 2016 10:46 AM