Use inputbox to send email note

[rickblunt]

Hello,
I use email subs routinely to pass WS/WB information around all the time. A request was generated that I add in a function so that users could send me a note about comments or suggestions on how certain WB's are working for the users; kind of like a "contact the webmaster" kind of thing. I was going to just whip up another userform but I was thinking that since it will only be a single string of text for the comment, and that it would always be coming to me; it would be more convenient if I just used an inputbox. I tweaked the code that I have been using for the inputbox function, but I am stumped on the part about the "activesheet" Since a WS may or may not be open, well I suppose there is always a WS open, but I am not taking any data from the WS, so I don't need to reference it. The text is coming from the inputbox so I need to figure out what syntax I use to call the mailenvelope, I tried "Inputbox.mailenvelope" and that didn't work.

Does anyone have any suggestions? I appreciate any ideas -thanks

Private Sub EmailGo_Click()


Dim myValue As Variant




myValue = InputBox("Please type in your comments", "Comments and Suggestions")


    With ActiveSheet.MailEnvelope
      .Introduction = " A comment or suggestion has been made about the Facilities Inspection App. "
      .Item.To = "first.name@company.com"
      .Item.Subject = "Inspection App Comment"
      .Item.Body = myValue
      .Item.Send
   End With
End Sub

 

[mikerickson]

If you need to reference a worksheet, ThisWorkbook.Sheets(1) always exists. Assuming that it has a MailEnvelope, just substitute that for ActiveSheet.

 

[rickblunt]

Thanks for replying mikerickson, in this case I am not referencing any sheet, I am referencing the inputbox. So I need to exchange (or remove entirely?) that reference so that the mailenvelope knows that the object is the inputbox. I tried to deleting it and it failed, I tried exchanging and I got an object error.

Maybe what I have worked up is totally wrong (wouldn't be the first time for me - lol) I am basically just trying to use an input box to send a single string of text via email. Is this possible?

 

[mikerickson]

What happened when you tried the code in the OP?

 

[rickblunt]

So I have tried a couple of things, swapping "activesheet" for "inputbox" gives me a "compile error:argument not optional"; so it sounds like I can't use an input box for the body of the text, guessing it is because vba does not recognize the "textbox" in the input box. I am hoping that I just need to use a different syntax.

Private Sub EmailComments()
Dim myValue As Variant

myValue = inputbox("Please type in your comments", "Comments and Suggestions")

With inputbox.MailEnvelope
      .Introduction = " A comment has been made about the Facilities Inspection Program "
      .Item.To = "first.last@company.com"
      .Item.Subject = "Facilities Inspection Program Comment"
      .Item.Send
   End With

End Sub

 

[mikerickson]

What happened when you left it alone and used the code as written in the OP?

 

[rickblunt]

You would just get whatever data happens to be on the worksheet that is open (or active) at the time....

 

[mikerickson]

You would just get whatever data happens to be on the worksheet that is open (or active) at the time....

Does that mean that you tried it and that is the result you actually got?
Or does that mean that is your estimate of what would happen?

 

[rickblunt]

That is exactly what I got