Hello everyone

I am trying to alter my sendmail code to send to an e-mail address as
specified in cell AO1 but cannot get it to work.

I have tried following Ron DeBruins tips for changing SendMail examples but
something is wrong.

Could someone look at my code and suggest what to do?

Regards

Kenny

Private Sub CommandButton11_Click()
' Thanks www.rondebruin.nl

Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim msg, Style, Title, Response, MyString

'added
Dim Myhome As Variant

'added
Myhome = Sheets("Lists").Range("AO1")

strdate = Format(Now, "yyyy-mm-dd")
Shname = Array("Data", "Data")

'changed to "Myhome"
Addr = Array("Myhome", ")

msg = "Are you sure you want to send H.O. the database?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Caution - Last chance to abort sending e-mail"
Response = MsgBox(msg, Style, Title)
If Response = vbYes Then

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name & " data back-up " _
& strdate & ".xls"
.SendMail Addr(N), _
"Defects data back-up" 'This is the subject line!
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
Else
Exit Sub
End If


End Sub

Oops!

Looks like I was a bit too quick in asking for help - Changed "Myhome" to
Myhome and it worked!!!

Sorry

Kenny


"N E Body" wrote in message
...
Hello everyone

I am trying to alter my sendmail code to send to an e-mail address as
specified in cell AO1 but cannot get it to work.

I have tried following Ron DeBruins tips for changing SendMail examples
but
something is wrong.

Could someone look at my code and suggest what to do?

Regards

Kenny

Private Sub CommandButton11_Click()
' Thanks www.rondebruin.nl

Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim msg, Style, Title, Response, MyString

'added
Dim Myhome As Variant

'added
Myhome = Sheets("Lists").Range("AO1")

strdate = Format(Now, "yyyy-mm-dd")
Shname = Array("Data", "Data")

'changed to "Myhome"
Addr = Array("Myhome", ")

msg = "Are you sure you want to send H.O. the database?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Caution - Last chance to abort sending e-mail"
Response = MsgBox(msg, Style, Title)
If Response = vbYes Then

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name & " data back-up " _
& strdate & ".xls"
.SendMail Addr(N), _
"Defects data back-up" 'This is the subject line!
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
Else
Exit Sub
End If


End Sub