Send email macro to multiple recipients

[laurainwa]

I'm using the macro listed below (from RondeBruin) to send a worksheet as an attachment. It works great, except I am sending it to multiple recipients and I have to click the "allow" program to send email for each and every email. Is there a way to just send one email to everyone I need? I need to know what part of this code to delete and what to replace it with. (I don't know really anything about VBA code so I need it in as simple terms as possible. )
Sub Mail_ActiveSheet()
'Working in 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010, we exit the sub when your answer is
'NO in the security dialog that you only see when you copy
'an sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheet to values if you want
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "ron@debruin.nl", _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Siyanna]

ANY macros that need to be sent with the sheet, will need to be in the sheet module.
You will then have to re-assign the macros to the buttons.
However, the macros seem to be a fairly simple method of locating a cell, which seems a bit of a waste of effort trying to attach them !!

Hi Michael,

How would you advise to do the highlighted part without attaching them?

 

[Michael M]

Why not simply tell them by E-mail what their data location is !!
Are the users not able to scroll through the sheet to locate their own data ?

 

[Siyanna]

Why not simply tell them by E-mail what their data location is !!
Are the users not able to scroll through the sheet to locate their own data ?

Hi Michael,

I wish i could make it that simple but my manager wants me to create buttons and have them navigate that way.

If only i could have just copied the module 2 over along with the sheet

I guess i have to copy the same macro on all sheets and assign them to each button... ****

P.s

Am i right in saying

Mail_Object.CreateItem(o) 'this part is like saying File (newmail)
Set OutMail = Nothing ' this part sets everything in the mail i am sending to blank
Set OutApp = Nothing ' not sure about this one

Thank you

You have been a great help....God bless you.<!-- / message --><!-- edit note --><!-- / message -->

 

[Michael M]

Am i right in saying

Mail_Object.CreateItem(o) 'this part is like saying File (newmail) Yes, new message
these two lines
Set OutMail = Nothing
Set OutApp = Nothing
Simply cancel these two lines
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Thank you

 

[Siyanna]

Am i right in saying

Mail_Object.CreateItem(o) 'this part is like saying File (newmail) Yes, new message
these two lines
Set OutMail = Nothing
Set OutApp = Nothing
Simply cancel these two lines
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Thank you

Hi Michael,

Are you saying to change this part of the code


Set OutMail = Nothing
Set OutApp = Nothing

to this

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


P.s

Is this an alphabet O or number 0? Mail_Object.CreateItem(o)?

The reason i say this is because this looks like the number 0
Set OutMail = OutApp.CreateItem(0) however this looks like the alphabet O in lower case
Mail_Object.CreateItem(o)?

Thank You

 

[Siyanna]

Hi Michael.

I have read on the mr excel site that you can copy a module but this involves exporting and importing macros (Don't have a clue on how this works).

May Be you might be more familiar with this expression. Here is the link

http://www.mrexcel.com/articles/copy-vba-module.php

 

[Michael M]

No...if you look at the code you will the Set lines, early in the code.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

To ensure these are "Switched off" at the end of the code, you use

Set OutMail = Nothing
Set OutApp = Nothing

to do it !!

Alphabet "o"

You seem to be asking a lot of questions regarding the lines of code.....Is this also some sort of study project !!

 

[Siyanna]

No...if you look at the code you will the Set lines, early in the code.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

To ensure these are "Switched off" at the end of the code, you use

Set OutMail = Nothing
Set OutApp = Nothing

to do it !!

Alphabet "o"

You seem to be asking a lot of questions regarding the lines of code.....Is this also some sort of study project !!

Sorry Michael....

I know i have asked enough questions and thanks to you i actually understand how this works rather than just copying the code and going on like i am a genius when i don't have a clue.

I like to understand rather than acting like a Mrs Know it all

 

[Michael M]

I've seen the thread you posted regarding copying VBA modules....but it seems an extraordinary amount of work to scroll down the sheet a few lines .....personally, not worth it !!
Maybe you should explain to your boss how much work is required, on your part, to do this, as opposed to the user scrolling down the page to find their work !!

 

[Siyanna]

I've seen the thread you posted regarding copying VBA modules....but it seems an extraordinary amount of work to scroll down the sheet a few lines .....personally, not worth it !!
Maybe you should explain to your boss how much work is required, on your part, to do this, as opposed to the user scrolling down the page to find their work !!

Thank You sir for all your help and explanation

I will see what my manager says....For now, i will just copy the same macro on all the sheets and and assign them to each button (Pain but hopefully i can get arond this by having a quick word)

Thank You once again.

I will test this code at work on Wednesday (Fingers crossed it should work Fine)