VB Help for Spreadsheet!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: VB Help for Spreadsheet!

Hybrid View

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    VB Help for Spreadsheet!

    [Originally posted by Lyn]

    I am trying to compile a spreadsheet which has to be sent out to 500 employees - for them to fill in and then send it back to a mail box which has been set up especially for them in Outlook.* The problem is we want the macro to be set up to send the sheet automatically without the 'Outlook mail message' coming up.* We also need our mail box to be in the 'To' box (of the email) and then for them to click 'Send' and then the sheet goes automatically.

    This is the code we have so far:


    Sub SendSummary()
    Sheets("Summary").Activate
    sendDate = Date
    Cells(5, 4) = sendDate
    collectDate = Cells(2, 4)
    If sendDate < collectDate Then
    * * Title = &quot;TRAC Data Gathering System&quot;
    * * MessX = &quot;Are you sure you want to send the summary before the end of the quarter&quot;
    * * YesNo = MsgBox(MessX, vbYesNo, Title)
    * * If YesNo = 6 Then
    * * * * sendAnyway = True
    * * Else
    * * * * sendAnyway = False
    * * End If
    Else
    * * sendAnyway = True
    End If
    If sendAnyway Then
    * * EENA = Cells(3, 4) 'cell D3
    * * Quarter = Cells(4, 4)
    * * Sheets(&quot;Summary&quot;).Select
    * * Sheets(&quot;Summary&quot;).Copy
    * * ActiveWorkbook.SaveAs Filename:=EENA & &quot;-04-Q&quot; & Quarter & &quot;.xls&quot;, _
    * * * * FileFormat:=xlNormal, Password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, _
    * * * * ReadOnlyRecommended:=False, CreateBackup:=False
    * * Application.Dialogs(xlDialogSendMail).Show
    * * ' now need to automatically create a mail message that send the sheet EENA-04-Qn.xls
    * * ' to some standard email address that has been set up to collect all this data
    * *
    * * ActiveWorkbook.Close
    * * Sheets(&quot;Teaching&quot;).Select


    Can anyone help?

    Thanks!

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:VB Help for Spreadsheet!

    [Originally posted by Ron Weller]

    xlDialogSendMail this dialog has three arguments
    recipients, subject, return_receipt
    * you place them after the show in the order
    displayed above.
    Application.Dialogs(xlDialogSendMail).Show "you@what.com","The subject text", "me@where.com"

    * All arguments are optional

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center