.send function with email script...

[chefdt]

I have a nice little email script that I typically run from the MACRO Editor on the Developer ribbon. I store the macro in my PERSONAL file, and it runs perfectly.

When I try to attach it to a button in another workbook, and activate that WB to run it on, it says it can't find who I want to send to.

Private Sub CommandButton2_Click()


'Works FINE without this code
'________________________________________________________
[B]Dim wb As Workbook, x As String[/B]
[B]For Each wb In Workbooks[/B]
[B]If wb.Name <> ThisWorkbook.Name Then x = wb.Name[/B]
[B]Next wb[/B]
[B]Workbooks(x).Activate[/B]
'_________________________________________________________


'LOOPER
Dim sht As Object
  For Each sht In Sheets
   If sht.Name <> "Summary" And sht.Name <> "Emails" Then
    sht.Activate
      With sht






Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String








'Turn off screen updating
Application.ScreenUpdating = False


'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook


'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs Filename:=LFileName


'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)


'Set mail attributes (uncomment lines to enter attributes)
' In this example, only the attachment is being added to the mail message
With oMail
.To = [b1].Value
.Subject = "RevMan Pricing WorkSheet TEST- " & LFileName
.body = "Jeff Benedek - RevMan Lead" & vbCrLf & vbCrLf & _
"Attached is the RevMan file!"
.Attachments.Add LWorkbook.FullName
[B].Send[/B]
End With


'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False


'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing




    
End With
    End If
Next sht






End Sub

 

[Leith Ross]

Hello chefdt,

Change This line...

    .To = [b1].Value

To This ...

    .To = .Range("B1").Value

 

[V_Malkoti]

Do you have an email address in cell B1 of the activesheet of that workbook?

 

[chefdt]

Yes, I do.

 

[chefdt]

Same Error - "We need to know who to send this to. Enter at least one name"