?
Solved

Excel VBA Email?

Posted on 2003-03-06
22
Medium Priority
?
464 Views
Last Modified: 2008-02-01
I have a user form that once completed is a single excel sheet.  This sheet will contain a list of email addresses.  The addresses are added as the sheet is passed from person to person via email.  If the person quits the job, I need the process to reverse so that each manager that filled out the sheet, gets a copy of the sheet back so they can remove the users privlages on various systems.  I also need some way to track this delete process so that it can be audited.  I thank you in advance.
0
Comment
Question by:rb0y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 3
22 Comments
 
LVL 13

Expert Comment

by:cri
ID: 8080954
This is a spec, not a question.
0
 

Author Comment

by:rb0y
ID: 8081498
Ok... How do I do all that in VBA for excel.
0
 

Author Comment

by:rb0y
ID: 8082695
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.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 13

Expert Comment

by:cri
ID: 8083353
I know this has to be done in VBA. Apparently you did not get _my_ message.
0
 

Author Comment

by:rb0y
ID: 8083580
Any questions just ask, and I am willing to give more points for good answers.
0
 
LVL 1

Expert Comment

by:njelger
ID: 8087600
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
0
 
LVL 1

Expert Comment

by:njelger
ID: 8087660
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
0
 

Author Comment

by:rb0y
ID: 8089701
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.  
0
 
LVL 13

Expert Comment

by:cri
ID: 8094125
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
0
 

Author Comment

by:rb0y
ID: 8103223
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.  
0
 
LVL 1

Expert Comment

by:njelger
ID: 8103375
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
0
 
LVL 1

Accepted Solution

by:
njelger earned 960 total points
ID: 8103397
This might actually work. (some typos in the previous one)

Add 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 oMail = oApp.CreateItem(olMailItem)

oMail.To = Your emailaddress
oMail.Subject = Your message title
oMail.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
0
 

Author Comment

by:rb0y
ID: 8103489
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.

0
 
LVL 1

Expert Comment

by:njelger
ID: 8103512
so what do you want?
0
 

Author Comment

by:rb0y
ID: 8103515
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.
0
 

Author Comment

by:rb0y
ID: 8103544
Is there a way in VBA to get a return recipt in outlook?
0
 
LVL 1

Expert Comment

by:njelger
ID: 8103554
ok, so you want to make an automatic chain-letter? Why don't you just put the emailaddresses in different cells?
0
 
LVL 1

Expert Comment

by:njelger
ID: 8103636
add this property to your mailitem  (newMail)

.ReadReceiptRequested = true

/j
0
 

Author Comment

by:rb0y
ID: 8103660
Is there a way to keep outlook from displaying the warnings about mail.
0
 
LVL 1

Expert Comment

by:njelger
ID: 8103693
the warnings that you are accesing the address-book?
0
 

Author Comment

by:rb0y
ID: 8103799
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.
0
 

Author Comment

by:rb0y
ID: 8106214
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?
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question