Solved

Send excel file from Lotus Notes

Posted on 2014-07-28
7
161 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 49

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 49

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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 49

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

786 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