Send Attached excel sheet to varied email addresses

[forrestgump]

I am currently trying to create VBA to send a specified excel sheet to varied email sources. I currently have the code below which sends the attachment in an email to a specified source e.g. [e-mail], however I want to change this so the address is chosen from a specified cell on a worksheet. I have tried to reference a cell e.g. .sendmail Sheets("sheet3").range("A1"),_ but this does not work. Any ideas anyone?

 Sub Mail_ActiveSheet() 
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
 
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
 
Set Sourcewb = ActiveWorkbook
 
'Copy the sheet to a new workbook
Sheets("Sheet2").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
'We exit the sub when your answer is NO in the security dialog that you only
'see when you copy a 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 = "Headcount " & Format(Now, "dd-mmm-yy h-mm-ss")
 
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "[e-mail]", _
"Headcount&Turnover"
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
 

[MMcCarthy]

Assuming Sheet3 is on the current workbook then try the following to reference the A2 ...

ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1)

[forrestgump]

Assuming Sheet3 is on the current workbook then try the following to reference the A2 ...

ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1)

Unfortunately this did not work but thanks for your reply.

[MMcCarthy]

Unfortunately this did not work but thanks for your reply.

Why didn't it work? The syntax for referencing the value in the cell is correct.

[forrestgump]

Why didn't it work? The syntax for referencing the value in the cell is correct.

I don't know why this does not work I don't get an error messgae but the code does not connect to outlook. below is the VBA I now use with your suggestion:-
.SendMail ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1), _
full code below. Am I doing something wrong?

Sub Mail_ActiveSheet()
'Working in 97-2007
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    Set Sourcewb = ActiveWorkbook
 
    'Copy the sheet to a new workbook
    Sheets("Sheet2").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
            'We exit the sub when your answer is NO in the security dialog that you only
            'see  when you copy a 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
        .SendMail ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1), _
                  "This is the Subject line"
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
 
    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

[MMcCarthy]

If Destwb is the Workbook where this cell is then change the code to ...

.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), "This is the Subject line"

[forrestgump]

If Destwb is the Workbook where this cell is then change the code to ...

.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), "This is the Subject line"

Unfortunately i still can get this to work. Do you know if you can definiently reference a cell with the .Send mail function?

[MMcCarthy]

Unfortunately i still can get this to work. Do you know if you can definiently reference a cell with the .Send mail function?

1. Test what this is returning

Destwb.Worksheets("Sheet3").Cells(2, 1)

2. If it is returning the email address then declare a string variable and pass the value to that first. Then use the variable in the sendmail function.

[forrestgump]

1. Test what this is returning

Destwb.Worksheets("Sheet3").Cells(2, 1)

2. If it is returning the email address then declare a string variable and pass the value to that first. Then use the variable in the sendmail function.

I'm sorry I am not very strong with VBA I am not quite sure what I need to do?

[MMcCarthy]

I'm sorry I am not very strong with VBA I am not quite sure what I need to do?

Put it in the immediate window and press return.

[forrestgump]

I get the following message:-

compile error:
Expected:=

[MMcCarthy]

I get the following message:-

compile error:
Expected:=

Try this ...

Destwb.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), _
"This is the Subject line"

[forrestgump]

Try this ...

Destwb.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), _
"This is the Subject line"

Unfortunately it still does not work. Whenever I try and refer to a cell on the workbook it does not connect to outlook. Strange!

Regards,

Forrest