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

Sami

Board Regular
Joined
Mar 4, 2002
Messages
122
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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???
 
Upvote 0
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.
 
Upvote 0
so theres no way of editing the code i just gave to attach the worksheet "ReOrderSheet" rather than the workbook thats its in????
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top