Solved

Send excel file from Lotus Notes

Posted on 2014-07-28
7
163 Views
Last Modified: 2014-07-29
From within the Excel file I am working I will create a new file and save it with a  name [i.e. ReconReport.xls]

I will then need to create a Lotus Notes e-mail to one or more recipients [but it may be better to have a pop up window that allows the user to add their own recipients rather than hard coding them if poosible]

I then need to attach the Excel file ReconReport.xls into Lotus Notes e-mail and for the e-mail to autosend to the recipients.

This sounds so easy but I know its not. Is there and Expert out there that could provide me with the VBA code to do this. I have searched EE but there is not an exact solution that I could work with.

Many thanks
0
Comment
Question by:Jagwarman
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40223654
Hi,

pls try

Sub Send_Sheets_Notes_Email()
 
  Const EMBED_ATTACHMENT As Long = 1454
 
  Dim varRecipients As Variant

 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
  Dim wbBook As Workbook

  On Error GoTo Error_Handling
 
  Application.ScreenUpdating = False
 
  Set wbBook = ThisWorkbook

   wbBook.Save
   strAttachment = wbBook.Path & "\" & wbBook.Name

   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")

   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

   Set noDocument = noDatabase.CreateDocument
   Set noAttachment = noDocument.CreateRichTextItem("myAttachment")
   Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", strAttachment)


   strRecipients = Application.InputBox( _
           Prompt:="Please add the name of the recipients.", _
           Title:="Recipients", Type:=2)
   If strRecipients = False Then Exit Sub
   
   varRecipients = Split(strRecipients, ";")

   With noDocument
     .Form = "Memo"
     .SendTo = varRecipients
     .Subject = "mySubject"
     .Body = "myBody"
     .SaveMessageOnSend = True
     .PostedDate = Now()
     .Send 0, varRecipients
   End With
 
  MsgBox ("The e-mail has successfully been created and distributed."), vbInformation
 
ExitSub:
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
  Exit Sub
 
Error_Handling:
  MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
  Resume ExitSub
End Sub

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 40223804
Rgonzo 1971, that is brilliant except [and this is probably down to the way I wrote out my request] it attached the file that Macro is in rather than the file that I create from the Macro.

So, I have a file with a macro, I then create a new file i.e.

Dim NewBook As Workbook

Set NewBook = Workbooks.Add
    With NewBook
        .Title = "ReconReport"
        .Subject = "ReconReport"
        .SaveAs Filename:="ReconReport.xls"
    End With


I then copy the 3 tabs from my original file to this new file and it is this new file that I want to send out.

Is it me am I doing something wrong?

Thanks in Advance.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40223847
pls try

    Set wbBook = ThisWorkbook


    wbBook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

    Set NewBook = ActiveWorkbook
    
    With NewBook
        .Title = "ReconReport"
        .Subject = "ReconReport"
        .SaveAs Filename:="ReconReport.xls"
    End With
    strAttachment = NewBook.Path & "\" & NewBook.Name
    newBook.Close

Open in new window

Change sheet names
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:Jagwarman
ID: 40224325
Hi Rgonzo1971,

Maybe it is me that is not understanding your replies to me but, your last reply so far as I can tell copies the data from my original file to the new file called ReconReport and then closes ReconReport file.

What I need is for the new file ReconReport to be sent out as the attachment.

Am I misunderstanding your code?

Again thanks in advance
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40224419
let's try
Sub Send_Sheets_Notes_Email()
 
  Const EMBED_ATTACHMENT As Long = 1454
 
  Dim varRecipients As Variant

 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
  Dim wbBook As Workbook

  On Error GoTo Error_Handling
 
  Application.ScreenUpdating = False
 
    Set wbBook = ThisWorkbook


    wbBook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

    Set NewBook = ActiveWorkbook
    
    With NewBook
        .Title = "ReconReport"
        .Subject = "ReconReport"
        .SaveAs Filename:="ReconReport.xls"
    End With
    strAttachment = NewBook.Path & "\" & NewBook.Name
    newBook.Close


   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")

   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

   Set noDocument = noDatabase.CreateDocument
   Set noAttachment = noDocument.CreateRichTextItem("myAttachment")
   Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", strAttachment)


   strRecipients = Application.InputBox( _
           Prompt:="Please add the name of the recipients.", _
           Title:="Recipients", Type:=2)
   If strRecipients = False Then Exit Sub
   
   varRecipients = Split(strRecipients, ";")

   With noDocument
     .Form = "Memo"
     .SendTo = varRecipients
     .Subject = "mySubject"
     .Body = "myBody"
     .SaveMessageOnSend = True
     .PostedDate = Now()
     .Send 0, varRecipients
   End With
 
  MsgBox ("The e-mail has successfully been created and distributed."), vbInformation
 
ExitSub:
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
  Exit Sub
 
Error_Handling:
  MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
  Resume ExitSub
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40226090
Rgonzo1971 that is exactly what I need. It's never until I see the end result of what I ask for that something else springs to mind and in this case I really should have said I needed the data on the ReconReport Tabs protected. If that is not possible not to worry you have already more than earned the 500 points. Thanks for your help with this one.
0
 

Author Closing Comment

by:Jagwarman
ID: 40226098
brilliant
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question