With ActiveSheet.Copy - issue

[george_nastase]

Hy !
I am trying to use code that automatically send every sheet from excel file to a specific email adress ( using Lotus Notes )...
I have tha master code that workes very fine when I call it from each sheet but when I want to call in every sheet by loop through all sheets...I cant understant why ...PLEASE HELP ME !!!

'Master code :_________________________________________________________
Option Explicit
 
Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "c:\Attachments"
Const stSubject As String = "Weekly report"
Const vaMsg As Variant = "The weekly report as per agreement." & vbCrLf & _
                                          "Kind regards," & vbCrLf & _
                                           "Dennis"
Const vaCopyTo As Variant = "name@mail.com"
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
    .Copy
    stFileName = .Range("A1").Value
  End With
 
  stAttachment = stPath & "" & stFileName & ".xls"
 
  'Save and close the temporarily workbook.
  With ActiveWorkbook
    .SaveAs stAttachment
    .Close
  End With
 
  'Create the list of recipients.
  vaRecipients = VBA.Array("name1@mail.com", "name1@mail.com")
 
  '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
 
'_______________________________________________________________________
'Secondary code :_________________________________________________________

      Sub WorksheetLoop()
         Dim WS_Count As Integer
         Dim I As Integer
         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count
         ' Begin the loop.
         For I = 1 To WS_Count
            ' Insert your code here.
            
            Call Send_Active_Sheet
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name
         Next I
      End Sub

 

[MARK858]

Your code is working with the active sheet but you aren't activating it in the secondary code.

You need

Worksheets(I).Activate

after the line below

For I = 1 To WS_Count

 

[george_nastase]

Your code is working with the active sheet but you aren't activating it in the secondary code.

You need

Worksheets(I).Activate

after the line below

For I = 1 To WS_Count

It still doesn't work...seems to be very logical what you said but my file has own personality
Any other ideea ?

10tks!

 

[MARK858]

Not at the moment as I can see nothing else obvious on my phone. I will have a look if no one else does when I get in.
In the meantime have you tried stepping through the code with F8 to see what is/isn't happening?

http://www.cpearson.com/excel/DebuggingVBA.aspx

 

[RoryA]

You haven't said what is actually going wrong?