Thread: Email worksheet (not entire workbook) to various recipients

I downloaded a macro to email a specific worksheet within a workbook, but you have to hard code the recipient. The macro works perfectly, its only drawback is the hard coded email addy. I use MS outlook, and I assume that the macro opens Outlook invisibly when it sends. Can anyone advise me on how to improve its functionality to allow the user to add their own "to" recipients? Ideally, I would like Outlook to open to allow the user to enter their own adresses, and then "send" as normal.

I have seen some of the "miracles" the gurus here have produced in excel, so Im sure this will be a breeze. Thanks in advance.

Below is the part of the macro that does the "send"

With Destwb
   .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
      On Error Resume Next
      For I = 1 To 3
         .SendMail "myemailaddy@somename.com", _
            "test send"
            If Err.Number = 0 Then Exit For
      Next I
      On Error GoTo 0
   .Close SaveChanges:=False
End With

Change "myemailaddy@somename.com" to "" and the user will be prompted via OL dialog.

Technically you are emailing a workbook in it's entirety, however, that workbook contains only the desired sheet(s) from the original source file