Auto Email Excel Form MS Excel 2003

ctownsen80
ctownsen80 used Ask the Experts™
on
When clicking this buton, I want to do the following.

See the attacked file:

Validation: I want the Name and Address fields to be filled out. If the name and address fields are left NULL, I want a message to pop up saying that the form cannot be sent until these fields are filled out.

Once these field are completed, upon clicking the Submit button,
I want the form to automically go to a desginated email address..not open in dialog mode. For this task, lets says its test@email.com.
After the email is sent, I want a the option to Close and Save the form to appear with a yes or no option.

Any takers? :)
Email-Form.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


good afternoon!

try something like this...

i hope i can give u some idea....




game-master
Sub EmailMe()
    
    On Error GoTo hell
    
    Dim strConfirm As String
    Dim objMsg As Object
    Dim objConfi As Object
    
    If Sheet1.Range("B3") = "" Or Sheet1.Range("B4") = "" Then
        MsgBox "Pleas enter the email address and the name."
    Else
        
        Set objMsg = CreateObject("CDO.Message")
        Set objConfi = CreateObject("CDO.Configuration")
        
        With objMsg
            Set .Configuration = objConfi
            .Subject = Sheet1.Range("B5")
            .to = Sheet1.Range("B4")
            .from = "alvindoliente@yahoo.com"
            .textbody = "This is a test email only"
            .addattachment "C:\Email-Form.xls"
            .send
        End With
        
        
    End If
    
    If Err.Number = 0 Then
        strConfirm = MsgBox("Message has been send. Do you want to close this file?", vbYesNo + vbQuestion, "CLOSE FILE")
        
        If strConfirm = vbYes Then
            'do ur code here.
        End If
        
    End If
    
    Set objMsg = Nothing
    Set objConfi = Nothing
    
    Exit Sub
    
hell:
    MsgBox "Unable to send the file. Unexpected error occur!", vbCritical
    Set objMsg = Nothing
    Set objConfi = Nothing
End Sub

Open in new window

Top Expert 2010

Commented:
Hello game-master,

With respect, posting your email address in the clear like that is a very bad idea.  I suggest you use the 'request
attention' link to ask the Mods to obfuscate it.

Regards,

Patrick

Author

Commented:
When I input the code and fill out all of the fields, I clicked the button. When I do that the following message pops up: "Unable to Send the File. Unexpected Error Occured !"

After filling in all of the fields and clicking "Submit" the email should autosend to the recipient.

Thanks,
Chris


good morning!

patrick,

Thanks for the concern... my fault...


game-master

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial