Solved

Send excel file from Lotus Notes

Posted on 2014-07-28
7
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 51

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 51

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 51

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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