Excel VBA Email?

This is a spec, not a question.

Ok... How do I do all that in VBA for excel.

Basically I want the form to have a pull down list, that says active, which does nothing, or inactive, which causes the form to be emailed to the next person on the email list, that person will note that they have removed their info, then it will pass on to the next person, till everything is deleted, and keep a record of what its done, and where its been.
Maybe have a box that the manager checks when he is done stating that his info is deleted.  Yet the form itself will still have all the information, it will just be a form.

I know this has to be done in VBA. Apparently you did not get _my_ message.

Any questions just ask, and I am willing to give more points for good answers.

I don't really understand what you need.

"I also need some way to track this delete process so that it can be audited"

do you need to track deletions in a Excelfile?

/ j

I don't really understand what you need.

"I also need some way to track this delete process so that it can be audited"

do you need to track deletions in a Excelfile?

/ j

Yes sorry that was a bit unclear,  In excel i just need it to be emailed from hop to hop in reverse.  The information on the excel sheet itself, will not be altered.  However, i would like it to have an area on teh sheet, or maybe a txt file, that will record that this manager has recieved the file, and the manager will have a button on the sheet that they hit to acknowlegde they did their job.  

rb0y, I would suggest you post a bid request at

http:\\www.rentacoder.com

as this is more of a work request rather than a question

It is not that complicated.  I just want a button on the sheet that they press when they have deleted the correct information.  And i want it to read in an email address and send itself.  

Adf this code to your button.

Dim oApp As New Outlook.Application
Dim oMail As Outlook.MailItem
dim sSubject as String
dim sBody as string

Set olMail = ol.CreateItem(olMailItem)

olMail.To = Your emailaddress
olMail.Subject = Your message title
olMail.Body = Your message
oMail.Send

Set oMail = Nothing
Set oApp = Nothing

You can of course make them select the Cell with the "deletee" and press the button and by doing so extract the info from it and send the mail. You could also include a protect/unprotect function to force them into mailing.  

// j

Sub SendMailMessage()

'this sends the form as a email attachment
   
   Dim ol As New Outlook.Application
   Dim ns As Outlook.NameSpace
   Dim newMail As Outlook.MailItem
   Dim MailAdd As String
   Dim recRecipient1 As String
   Dim tempFilename As String
   Dim Sure As String
   
   Sure = MsgBox("Are you sure you are ready to email this form?", vbYesNoCancel, "Email User Form")
    If Sure <> vbYes Then
        MsgBox ("Please finish form.")
        End
    End If
   
   
   tempFilename = "c:\Temp\UserFormTest.xls"
   ActiveWorkbook.SaveAs tempFilename
   
   MailAdd = InputBox("Please enter the email address of the person you want to send this user form to.", "Email Address")
   
   recRecipient1 = MailAdd
   
If InStr(recRecipient1, "@") = 0 Then
    MsgBox ("That is not a valid email address.")
    End
End If

   'Return a reference to the MAPI layer
   Set ns = ol.GetNamespace("MAPI")

   'Create a new mail message item
   Set newMail = ol.CreateItem(olMailItem)
   With newMail
       'Add the subject of the mail message
       .Subject = "New User"
       'Create some body text
       .Body = "New User Form Is Attached" & vbCrLf

       'Add a recipient and test to make sure that the
       'address is valid using the Resolve method
       With .Recipients.Add(recRecipient1)
           .Type = olTo
           If Not .Resolve Then
               MsgBox "Unable to resolve address.", vbInformation
               
               GoTo Release_on_error
           End If
       End With

       

       'Attach a file as a link with an icon
       With .Attachments.Add _
           (tempFilename)
           .DisplayName = "Job Request"
       End With

       'Send the mail message
       .Send
   End With


  'Release memory
   Set ol = Nothing
   Set ns = Nothing
   Set newMail = Nothing
   
   Exit Sub

Release_on_error:
   
   'Release memory
   Set ol = Nothing
   Set ns = Nothing
   Set newMail = Nothing

End Sub


That is my email code.

so what do you want?

I am thinking, I make invisible buttons, then do a auto_open event to make each person opening the form put in who they are and their password.  Then the button that applies to them appears.  When they are done with the form, they click on the button to acknowledge they did it.  Then the form is emailed to the next person on the list.  One problem i have is extracting one email address out of a string of email address.

Is there a way in VBA to get a return recipt in outlook?

ok, so you want to make an automatic chain-letter? Why don't you just put the emailaddresses in different cells?

add this property to your mailitem  (newMail)

.ReadReceiptRequested = true

/j

Is there a way to keep outlook from displaying the warnings about mail.

the warnings that you are accesing the address-book?

Any time VBA access OUtlook, outlook says that there is a program trying to send mail and asks if it is ok.
I am also having trouble getting it to take a string of addresses, it says it cant resolve them.

I think the best thing would be to have my orginial excel file, access the saved sheet and open it up for editing.  So how do i get excel to open a saved sheet?