Emailing a sheet. - this should b easy - kinda urgent!
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Emailing a sheet. - this should b easy - kinda urgent!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Ive got this code for emailing the sheet ReOrderSheet when the button is pressed:

    Sub EmailSupplierWundpet()
    '
    ' EmailSupplierWundpet Macro
    ' Emails Wundpet Ltd. the Re-order list
    '

    Set aEmail = aOutlook.CreateItem(olMailItem)

    aEmail.Subject = "Oscarsons Pets & Provisions Re-Order List"

    aEmail.Body = "Credit card number =."

    aEmail.Attachments.Add ThisWorkbook.Path & "\data01.xls"

    aEmail.Recipients.Add "orders@wundput.co.uk"

    aEmail.Send

    MsgBox "Email successfully sent."

    End Sub

    But this line keeps coming up in yellow and i dont know how to fix it? is there an easier way to doing this. is this code wrong?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    plzzz anyone!?!?!?!

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ........ :s

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the code I use to e-mail a workbook.

    Sub SendIt()
    Application.Dialogs(xlDialogSendMail).Show _
    arg1:="e-mail address", _
    arg2:="filename"

    End Sub

    HTH
    I appreciate the help from everyone at Mr. Excel.

    viper

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sami,
    Is this routine any use?

    I think this is what you want and you can pick up the credit card etc my dim variables as range etc.

    Sub sendsheet1()
    'Declare String
    Dim sAddress As String
    Dim ssubject As String
    'Initialise the string with the address entered into box
    sAddress = "orders@wundpet.co.uk"
    ssubject = "Oscarsons Pets & Provisions Re-Order List"
    ' select the sheet within the spreadsheet
    Sheets("site 1").Select
    ' selects the named range within sheet
    Application.Goto Reference:="export1"
    Selection.Copy
    ' uses either a template file, important if you have lots of formatting or adds work book if simple
    Workbooks.Open FileName:="d:RSSL cost prosite1.xls"
    ' Workbooks.Add
    ' Selection.PasteSpecial Paste:=xlPasteAll ' pastes all formulas etc if reuired or use :-
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False ' paste values only

    Application.CutCopyMode = False
    ActiveWorkbook.SendMail Recipients:=sAddress , Subject:= ssubject, ReturnReceipt:=False
    MsgBox "Your email has been sent to" & vbCr & sAddress, , "MESSAGE"
    ActiveWindow.Close ' close the template or added workbook and ask if you want to save
    ' reselects the sheet with the macro
    Sheets("printing").Select
    Range("G22").Select
    End Sub


    [ This Message was edited by: keithkemble on 2002-03-16 09:50 ]

    [ This Message was edited by: keithkemble on 2002-03-16 10:19 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    this one doesnt seem to work because the e-mail address will always be "orders@wundpet.co.uk" and the subject will always be "Oscarsons Pets & Provisions Re-Order List" . Can anyone plz give me the code so that outlook express will open put in the subject and email address and not send as the user will have to enter his credit card number. Also how excel can attach the worksheet "ReOrderSheet" into the attachments of the email??? This sheet is in the Stock workbook.
    The code below needs to b altered i think? anyone plzzzzz and thanks 4 d ppl b4 who helped!!!

    Sub EmailSupplierWundpet()
    '
    ' EmailSupplierWundpet Macro
    ' Emails Wundpet Ltd. the Re-order list
    '

    Set aEmail = aOutlook.CreateItem(olMailItem)

    aEmail.Subject = "Oscarsons Pets & Provisions Re-Order List"

    aEmail.Body = "Credit card number =."

    aEmail.Attachments.Add ThisWorkbook.Path & "\\data01.xls"

    aEmail.Recipients.Add "orders@wundput.co.uk"

    aEmail.Send

    MsgBox "Email successfully sent."

    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HEYYYYYY IVE COMBINED SOME OF YOUR CODES - AND MOSTLY BY ACCIDENT IT WORKS!!!!!!!!!!!! THANKS BUT!!! heres the code:

    Sub EmailSupplierWundpet()
    '
    ' EmailSupplierWundpet Macro
    ' Emails Wundpet Ltd. the Re-order list
    '

    Application.Dialogs(xlDialogSendMail).Show _
    arg1:="orders@wundpet.com", _
    arg2:="Oscarsons Pets & Provisions Re-Order List"

    Set aEmail = aOutlook.CreateItem(olMailItem)

    aEmail.Subject = "Oscarsons Pets & Provisions Re-Order List"

    aEmail.Body = "Credit card number =."

    aEmail.Attachments.Add ThisWorkbook.Path & "\\data01.xls"

    aEmail.Recipients.Add "orders@wundput.co.uk"

    aEmail.Send

    MsgBox "Email successfully sent."

    End Sub

    it attaches the stock workbook and i only want it to attach the ReOrderSheet sheet inside the stock workbook? can i do this???

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have modded the previous code to reflect wunderpet etc,

    If you take the sub routine to copy and paste to a template you will achieve the desired result.

    You can also add an input box for the credit card details and place this in the temporary template.

    Your email will then be sent.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    so theres no way of editing the code i just gave to attach the worksheet "ReOrderSheet" rather than the workbook thats its in????

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sami,

    I think I have the code you need. I will get home around midnight and will test it then send it then.

    I've been working on the same type of project and only need the code to delete the file after sending it.

    viper

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com