emailing a single worksheet from an Excel VB form?

[jsmarsha]

Hi everyone,

I am pretty new at this and I was wondering if you could help me out.

I created a VB form that allows the user to input their email account
which will be used to send them a 'verification' code. The
verification code is created using a random number macro. Is there any
way to email a single worksheet to an email address? Another option
that might work a little better is to email the text from one cell to
the email address. Any idea how to do this?!

Here is what I have for coding so far (please don't laugh):

Private Sub CommandButton3_Click()
Randomize
MyNumber = Int((1000000 - 1 + 1) * Rnd + 1)
Sheet7.Range("f1").Value = "R" & MyNumber
Sheet8.Range("a1").Value = "Your Verification Code is: " &
Sheet7.Range("f1").Value
Sheet8.Range("a2").Value = ""
''''''''''
ActiveWorkbook.SendMail Recipients:=sheet7.Range("d1").value
''''''''''
'Sheet8.SendMail Recipients:=Sheet7.Range("d1").Value (could I use
this line as a
'subst.for the line immediately above?
End Sub

Hopefully I am not too far off. Also, would I have to send this using
Outlook?

Thank you for your help!

 

[Ron de Bruin]

Hi

Start here
http://www.rondebruin.nl/sendmail.htm

 

[jsmarsha]

I was thinking more about this. Is it possible to send an email
without ANY workbooks/worksheets attached? All I would really need is
to set the subject line to equal the contents of one cell.

I've tried to work through some of the examples on your web page and it
seems like Outlook is trying to email the account I've chosen, but for
some reason it isn't transmitting. The other problem I have is that
the recipients that I will be sending this to probably wouldn't be
using Outlook anyway.

Any thoughts about this?

 

[Ron de Bruin]

Hi

Yes you can do this with Outlook
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

For subject you can use
..Subject = ThisWorkbook.Sheets("Sheet1").Range("C1").Value

If you have problems then see
http://www.rondebruin.nl/mail/problems.htm

 

[jsmarsha]

Hey Ron,
Thanks for responding back so quickly!

Example #1 from your first link looks like it should do the trick, but
I'm getting flagged with an error: "User-defined type not defined"

I'm not really sure what this means. Any idea what is happening here?

Jeff

 

[Ron de Bruin]

Read this on the page


You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number