Send specific excel worksheet as attachment in lotus notes email to recipients defined in a separate worksheet of the workbook

lakshmi.acharya · Jun 16, 2011

Hi,

I am trying to automate the distribution of an internal sales report to different individuals in the organization. I am using Excel 2003 and Lotus 6.5. I have no knowledge of VB/VBA but I'm sure that this can be automated through a macro and hence seeking the help of experts here.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

> </o

>
My workbook is named “Sales Data.xls” and is stored in the location D:\Sales Data. Sales Data.xls has 2 worksheets namely: ‘Master’ and ‘Recipients’
<o

> </o

>
Master worksheet has sales information (region, city, product, stock, sales quantity, cost, net sales price) for different cities in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com

ffice:smarttags" /><st1:country-region w:st="on">India</st1:country-region> (Mumbai, <st1:City w:st="on">Delhi</st1:City>, <st1:City w:st="on"><st1

lace w:st="on">Bangalore</st1

lace></st1:City>, Chennai etc.). The City names are in Column B of this worksheet.
<o

> </o

>
I have used a code available on this forum to split the data in ‘Master’ into separate worksheets for each City and each worksheet is named after the City. So, once I run this macro, I have the following worksheets in my workbook:
Recipient, Master, Mumbai, Delhi, Bangalore, Chennai, etc.<o

></o

>
<o

> </o

>
‘Recipient’ has email IDs of individuals who are responsible for the sales in each of these cities and I need to be able to send them an email with data for just their city. Information in worksheet ‘recipient’ is stored as below:
<o

> </o

>
Column A Column B
Mumbai xyz@email.com<o

></o

>
<st1:City w:st="on"><st1

lace w:st="on">Delhi</st1

lace></st1:City> abc@email.com<o

></o

>
<st1:City w:st="on"><st1

lace w:st="on">Bangalore</st1

lace></st1:City> pqr@email.com<o

></o

>
Chennai mno@email.com<o

></o

>
<o

> </o

>
Now, I need a macro to automate the distribution of the respective worksheet as attachment to the defined recipient through Lotus Notes. However, the worksheet ‘Master’ and ‘Recipient’ should not be distributed to anyone.<o

></o

>
<o

> </o

>
If the content of the email can be automated as well, it will be helpful.<o

></o

>
Subject: Sales Report for <date> (today’s date)<o

></o

>
Email body: <o

></o

>
Please review the sales report for <date> (today’s date).<o

></o

>
<o

> </o

>
“Attachment” (worksheet name.xls) (eg: Mumbai.xls)<o

></o

>
<o

> </o

>
Regards,<o

></o

>
Lakshmi<o

></o

>
<o

> </o

>
I would tremendously appreciate any help that I can receive in accomplishing this task.<o

></o

>
<o

> </o

>
Thanks and regards,<o

></o

>
Lakshmi<o

></o

>

John_w · Jun 16, 2011

This code saves each 'city' sheet as a separate workbook containing just that sheet:

Code:

Option Explicit

Sub Save_City_Sheets()

    'Save each 'city' sheet in the current workbook as a separate workbook containing just that sheet

    Dim saveInFolder As String
    Dim sh As Worksheet
    
    saveInFolder = "C:\Temp\Excel\"     'CHANGE AS REQUIRED
    
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Master" And sh.Name <> "Recipients" Then
        
            'Create a new workbook containing just this sheet
            sh.Copy
            
            'Suppress prompt if file already exists
            Application.DisplayAlerts = False
    
            'Save the single sheet as a workbook
            ActiveWorkbook.SaveAs saveInFolder & sh.Name
            
            'Reinstate prompts
            Application.DisplayAlerts = False
                   
            ActiveWorkbook.Close False
        End If
    Next
    
End Sub

Then combine this with code from http://www.rondebruin.nl/notes.htm to achieve your requirement, or search this forum for creating/sending email via Lotus Notes.

lakshmi.acharya · Jun 17, 2011

Thank you, John. I now have a macro that I'm able to use in several other situations as well!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
Your macro is helping me split the 'City' worksheets into several workbooks. As suggested by you I also checked Ron's post for the macro to send the email by Lotus Notes. <o

></o

>
<o

> </o

>
Two macros seemed to fit my situation:<o

></o

>
<o

> </o

>
1. Send the active sheet as an attachment: Failed at the below code (in red)<o

></o

>
<o

> </o

>
stAttachment = stPath & "\" & stFileName & ".xls"<o

></o

>
<o

></o

>
'Save and close the temporarily workbook.<o

></o

>
With ActiveWorkbook<o

></o

>
.SaveAs stAttachment<o

></o

>
.Close<o

></o

>
End With<o

></o

>
<o

></o

>
2. Send worksheets to several recipients: I think the below code needs to be modified to read the worksheet name and the respective email ID as defined in ‘Recipient’ worksheet.<o

></o

>
<o

> </o

>

'Retrieve the list of recipients.<o

></o

></PRE>
With wsSheet<o

></o

></PRE>
lnLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<o

></o

></PRE>
vaRecipients = .Range("A1:A" & lnLastRow).Value<o

></o

></PRE>
End With<o

></o

></PRE><o

> </o

>
Sorry, I'm extremely new to this and hence I was not able to figure out how each 'City' worksheet can get distributed to the list of recipients defined in my 'Recipient' workbook. I'm still hopeful that this can be accomplished by a macro but maybe I'm overreaching?!<o

></o

>
<o

></o

>
Any help would be much appreciated!!<o

></o

>
<o

></o

>
Regards,<o

></o

>
Lakshmi<o

></o

>
<o

> </o

>

John_w · Jun 17, 2011

lakshmi.acharya said:
1. Send the active sheet as an attachment: Failed at the below code (in red)

What's the error? What's the value of stAttachment? I suspect an invalid file name or trying to save to a non-existent folder. Try debugging the code in the VB editor using the Locals window or the Immediate window (use the View menu to display these windows). Type ?stattachment in the Immediate window to show its value.

For looping through the cities and email addresses on the Recipients sheet, try something like:

Code:

    Dim lastRow As Long, row As Long
    
    With Sheets("Recipients")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        For row = 2 To lastRow   'start at row 2 because row 1 is assumed to contain column headings
            MsgBox "City: " & .Cells(row, "A") & vbNewLine & "Email: " & .Cells(row, "B")
        Next
    End With

lakshmi.acharya · Jun 20, 2011

Here is my code for sending the email after I have created different worksheets for each "City". It however, still does not send the email

. I'm hoping you'll be able to review and let me know what is it that I'm doing wrong. I feel the vaRecipient defined in the code is somehow incorrect but I could very well be wrong.

The macro loops thru all the cities and displays the City Name and the Respective Email ID where the email needs to be sent. But the email does not get sent from Lotus. I'm assuming the macro just shows the City and email from the 'Recipients" sheet. When I debug the macro, the error message I get is: "Subscript out of range"

My code (copied from Run's website and with your inputs) starts here:

Sub Send_Sheets_Notes_Email()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
'Notes parameter for attaching the Excel files.<o

></o

>
Const EMBED_ATTACHMENT As Long = 1454<o

></o

>
<o

></o

>
'A folder to temporarily store the created Excel files in.<o

></o

>
Const stPath As String = "D:\Attachments"<o

></o

>
<o

></o

>
'The subject for the outgoing e-mails.<o

></o

>
Const stSubject As String = "Sales Report"<o

></o

>
<o

></o

>
'The message in the bodies of the outgoing e-mails.<o

></o

>
Const vaMsg As String = "The Sales Report is attached for your review." & vbCrLf & _<o

></o

>
"Regards," & vbCrLf & _<o

></o

>
"Lakshmi"<o

></o

>
<o

></o

>
'Variable that holds the list of recipients for each worksheet.<o

></o

>
Dim vaRecipients As Variant<o

></o

>
<o

></o

>
<o

></o

>
'Variable which holds each worksheet's name.<o

></o

>
Dim stFileName As String<o

></o

>
<o

></o

>
'Variables for Notes.<o

></o

>
Dim noSession As Object<o

></o

>
Dim noDatabase As Object<o

></o

>
Dim noDocument As Object<o

></o

>
Dim noEmbedObject As Object<o

></o

>
Dim noAttachment As Object<o

></o

>
Dim stAttachment As String<o

></o

>
<o

></o

>
'Variables for Excel.<o

></o

>
Dim wbBook As Workbook<o

></o

>
Dim wsSheet As Worksheet<o

></o

>
Dim lnLastRow As Long<o

></o

>
Dim row As Long<o

></o

>
<o

></o

>
On Error GoTo Error_Handling<o

></o

>
<o

></o

>
Application.ScreenUpdating = False<o

></o

>
<o

></o

>
With Sheets("Recipient")<o

></o

>
InlastRow = .Cells(.Rows.Count, "A").End(xlUp).row<o

></o

>
For row = 2 To InlastRow 'start at row 2 because row 1 is assumed to contain column headings<o

></o

>
MsgBox "City: " & .Cells(row, "A") & vbNewLine & "Email: " & .Cells(row, "B")<o

></o

>
<o

></o

>
Set wbBook = ThisWorkbook<o

></o

>
<o

></o

>
'Loop through the collection of worksheets in the workbook.<o

></o

>
For Each wsSheet In wbBook.Worksheets<o

></o

>
With wsSheet<o

></o

>
'Copy the worksheet to a new workbook.<o

></o

>
.Copy<o

></o

>
'Retrieve the worksheet's name.<o

></o

>
stFileName = .Name<o

></o

>
End With<o

></o

>
<o

></o

>
'Create the full path and name of the workbook.<o

></o

>
stAttachment = stPath & "\" & stFileName & ".xls"<o

></o

>
<o

></o

>
'Save and close the temporarily workbook.<o

></o

>
With ActiveWorkbook<o

></o

>
.SaveAs stAttachment<o

></o

>
.Close<o

></o

>
End With<o

></o

>
<o

></o

>
'Instantiate the Lotus Notes COM's Objects.<o

></o

>
Set noSession = CreateObject("Notes.NotesSession")<o

></o

>
Set noDatabase = noSession.GETDATABASE("", "")<o

></o

>
<o

></o

>
'If Lotus Notes is not open then open the mail-part of it.<o

></o

>
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL<o

></o

>
<o

></o

>
'Create the e-mail and add the attachment.<o

></o

>
Set noDocument = noDatabase.CreateDocument<o

></o

>
Set noAttachment = noDocument.CreateRichTextItem("stAttachment")<o

></o

>
Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)<o

></o

>
<o

></o

>
'Add values to the created e-mail main properties.<o

></o

>
With noDocument<o

></o

>
.Form = "Memo"<o

></o

>
.SendTo = vaRecipients<o

></o

>
.Subject = stSubject<o

></o

>
.Body = vaMsg<o

></o

>
.SaveMessageOnSend = True<o

></o

>
.PostedDate = Now()<o

></o

>
.Send 0, vaRecipients<o

></o

>
End With<o

></o

>
'Delete the temporarily workbook.<o

></o

>
Kill stAttachment<o

></o

>
Next wsSheet<o

></o

>
<o

></o

>
<o

></o

>
MsgBox ("The e-mails have successfully been created and distributed."), vbInformation<o

></o

>
<o

></o

>
Next<o

></o

>
End With<o

></o

>
<o

></o

>
<o

></o

>
ExitSub:<o

></o

>
'Release objects from memory.<o

></o

>
Set noEmbedObject = Nothing<o

></o

>
Set noAttachment = Nothing<o

></o

>
Set noDocument = Nothing<o

></o

>
Set noDatabase = Nothing<o

></o

>
Set noSession = Nothing<o

></o

>
<o

> </o

>
<o

></o

>
Exit Sub<o

></o

>
<o

></o

>
Error_Handling:<o

></o

>
MsgBox "Error number: " & Err.Number & vbNewLine & _<o

></o

>
"Description: " & Err.Description, vbOKOnly<o

></o

>
Resume ExitSub<o

></o

>
End Sub

John_w · Jun 20, 2011

vaRecipients is declared but not defined and is used in the Lotus SendTo and Send. Therefore I think you need:

vaRecipients = .Cells(row, "B")

immediately after the first MsgBox to define it as the email address for that row.

Also, you have nested For...Next loops which will send all the worksheets to each recipient. I think you need to remove the inner For...Next loop.

lakshmi.acharya · Jun 21, 2011

Yeah - you are right! Once the vaRecipients got defined the email started pouring in. But on the flip side, like you also suggested, the email with each worksheet is also being sent to each recipient.

I am not sure I understood how to remove the inner 'for' and 'next' loop. I don't think I can just delete the 'for' ? I'm sorry to be such a novice at this but you've been a tremendous help and I've been learning the logic as and when you've responded. I just do not know the keywords and hence lost at what should be done to change the inner loop. Could you please help, yet again?

John_w · Jun 21, 2011

Just delete the inner For and Next statements, along with the code related to the worksheets, which isn't needed. Put the Lotus Notes initialisation before the remaining loop because it only needs to be done once. Here's the code (untested) with those changes:

Code:

Sub Send_Sheets_Notes_Email()

    'Notes parameter for attaching the Excel files.
    Const EMBED_ATTACHMENT As Long = 1454
    
    'A folder to temporarily store the created Excel files in.
    Const stPath As String = "D:\Attachments"
    
    'The subject for the outgoing e-mails.
    Const stSubject As String = "Sales Report"
    
    'The message in the bodies of the outgoing e-mails.
    Const vaMsg As String = "The Sales Report is attached for your review." & vbCrLf & _
    "Regards," & vbCrLf & _
    "Lakshmi"
    
    'Variable that holds the list of recipients for each worksheet.
    Dim vaRecipients As Variant
    
    
    'Variable which holds each worksheet's name.
    Dim stFileName As String
    
    'Variables for Notes.
    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject As Object
    Dim noAttachment As Object
    Dim stAttachment As String
    
    'Variables for Excel.
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim lnLastRow As Long
    Dim lastRow As Long, row As Long
    
    'Instantiate the Lotus Notes OLE 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
    
    With Sheets("Recipient")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        For row = 2 To lastRow 'start at row 2 because row 1 is assumed to contain column headings
            MsgBox "City: " & .Cells(row, "A") & vbNewLine & "Email: " & .Cells(row, "B")
            
            vaRecipients = .Cells(row, "B")
            stAttachment = stPath & "\" & .Cells(row, "A") & ".xls"
            
            'Create the e-mail and add the attachment.
            Set noDocument = noDatabase.CreateDocument
            Set noAttachment = noDocument.CreateRichTextItem("Attachment")
            Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
        
            'Add values to the created e-mail main properties.
            With noDocument
                .Form = "Memo"
                .SendTo = vaRecipients
                .Subject = stSubject
                .Body = vaMsg
                .SaveMessageOnSend = True
                .PostedDate = Now()
                .Send 0, vaRecipients
            End With
        Next
    
        MsgBox ("The e-mails have successfully been created and distributed."), vbInformation
    End With
    
    'Release objects from memory.
    Set noEmbedObject = Nothing
    Set noAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
    
End Sub

lakshmi.acharya · Jun 22, 2011

John,

You are a STAR!! Your code worked like a charm

.

Thanks much!!

Best regards,
Lakshmi

SkyeS · May 21, 2015

I've been trying to change this code to work for Outlook but i keep getting errors. Can you please help me?

This is the only part I changed. I've been playing around with it all day and yesterday and I just can't find any resources to help me. Also stupid question but is D:\ mean the desktop because thats where I want it to store the PDFs. Or is that another Drive?

'Instantiate the Lotus Notes OLE Objects.
Set noSession = CreateObject("Outlook.Application")
Set noDatabase = noSession.CreateItem(0)

I know this is a really old thread but I am really hoping you can help me.

Thank you!

Send specific excel worksheet as attachment in lotus notes email to recipients defined in a separate worksheet of the workbook

lakshmi.acharya

New Member

Excel Facts

John_w

MrExcel MVP

lakshmi.acharya

New Member

John_w

MrExcel MVP

lakshmi.acharya

New Member

John_w

MrExcel MVP

lakshmi.acharya

New Member

John_w

MrExcel MVP

lakshmi.acharya

New Member

SkyeS

New Member

Similar threads

Forum statistics

Share this page

Send specific excel worksheet as attachment in lotus notes email to recipients defined in a separate worksheet of the workbook

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock