PDA

View Full Version : Sleeper: Email Worksheet



v_gyku
09-08-2005, 12:21 AM
Hi everyone!

I know the solution for this is given in the solutions. But my problem is bit diffrerent.
I have a gui It contains following components.

1. Select Worksheet to email(All he sheets comes in a listbox we can select any on of it.)

2. Select recipeient to whom u want to mail (I am getting the list from outlook contacts, But i am getting it in this form---- lastname,firstname----)


3.Enter message for the body


4. Enter subject

5. Send worksheet



Now i want to send the selected worksheet to the recipient in the textbox,In text box i dont have his/her email id so i have to take it from outlook by matching the name and lastname.

can u give me the solution

v_gyku
09-08-2005, 02:39 AM
I have this code can u modify it to meet the rquirement...
I am selecting the name of the worksheet from the listbox on gui.
I am developing a activex component.



Private Sub cmdsend_Click()
If txtFileLocation.Text = "" Then
MsgBox "Please select a workbook.", vbOKOnly, "RightAnswers"
cmdbrowsebook.SetFocus
ElseIf List1.Text = "" Then
MsgBox "Please select a worksheet.", vbOKOnly, "RightAnswers"
ElseIf Txtname.Text = "" Then
MsgBox "Please select an email recipient.", vbOKOnly, "RightAnswers"
End If
Dim OL As Object ' Outlook instantiation
Dim EmailItem As Object ' A new mail item (e-mail)
Dim FileName As String ' The name of the file we are attaching
Dim lngLoop As Long ' counter for the FileName length
Dim TempChar As String ' used for the loop to test for unusable characters
Dim SaveName As String ' Attachment's new name, after cleaning
Application.ScreenUpdating = False ' speed up Excel processing time
' Set OL = CreateObject("Outlook.Application") ' New Outlook application
Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem
FileName = List1.Selected.name & " - " & ActiveWorkbook.name ' create a filename on the fly
For lngLoop = 1 To Len(FileName) ' error check for unusable chars in the filename
TempChar = Mid(FileName, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", ">", "/"
Case Else
SaveName = SaveName & TempChar
End Select
Next lngLoop
ActiveSheet.Cells.Copy ' copy the contents of the ActiveSheet
Workbooks.Add ' create a new workbook
Selection.PasteSpecial Paste:=xlValues ' paste the worksheet values into the new book
Selection.PasteSpecial Paste:=xlFormats ' and their formats
ActiveWorkbook.SaveAs "C:\" & SaveName ' temp file attachment location
ActiveWorkbook.ChangeFileAccess xlReadOnly ' make access read only
With EmailItem ' with the newly created e-mail
.Subject = txtsubject.Text
.Body = txtmessage.Text
.To = Txtname.Text
.Attachments.Add "C:\" & SaveName
.Send ' send the worksheet
End With
Kill "C:\" & SaveName ' delete the temporary attachment
ActiveWorkbook.Close = False ' close down the workbook without saving (single sheet)
Application.ScreenUpdating = True ' always remember to switch it back on!!
Set myolap = Nothing ' clean down memory
Set EmailItem = Nothing
End Sub

Norie
09-08-2005, 06:38 AM
Why not just include the email address in the listbox, perhaps in a hidden column?

v_gyku
09-08-2005, 09:05 PM
Its the requirement

v_gyku
09-09-2005, 12:27 AM
I am getting error at this line : FileName = Sheets(List1).Text '& " - " & ActiveWorkbook.name

For each selected worksheet, create a temporary workbook with the file name ?<worksheet name>.xls
i have created workbook but how will i keep the sheet in that folder.I am just displaying the name of the folder





Private Sub cmdsend_Click()
If txtFileLocation.Text = "" Then
MsgBox "Please select a workbook.", vbOKOnly, "RightAnswers"
cmdbrowsebook.SetFocus
ElseIf List1.Text = "" Then
MsgBox "Please select a worksheet.", vbOKOnly, "RightAnswers"
ElseIf Txtname.Text = "" Then
MsgBox "Please select an email recipient.", vbOKOnly, "RightAnswers"
ElseIf txtsubject.Text = "" Then
MsgBox "Please enter an email subject."
Else
If MsgBox("The <number of selected worksheets> Excel worksheets will be sent to the <number of selected email recipients> selected email address(es). Would you like to continue?", vbYesNo, "RightAnswers") = vbNo Then
MsgBox ("The selected worksheets will not be emailed to the selected recipients.")
End If


Dim Num As Long
Dim MaxNum As Long
Dim MinNum As Long
Dim Path As String

MinNum = 1
MaxNum = 1000
Path = folderpath & "RightAnswersTempWorkbooks"
On Error Resume Next
MkDir Path
Err.Clear
Randomize
Do
Num = Int((MaxNum - MinNum + 1) * Rnd + MinNum)
MkDir Path & "\RightAnswersTempWorkbooks" & Num
If Err = 0 Then
Exit Do
Else
Err.Clear
End If
Loop
On Error GoTo 0
MsgBox "Folder Created: " & Path & "\RightAnswersTempWorkbooks" & Num

Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim FileName As String
Dim y As Long
Dim TempChar As String
Dim SaveName As String

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
FileName = Sheets(List1).Text '& " - " & ActiveWorkbook.name

For y = 1 To Len(FileName)
TempChar = Mid(FileName, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", ">", "|", ":"
Case Else
SaveName = SaveName & TempChar
End Select
Next y
Sheets(List1).Text.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs SaveName
Wb.ChangeFileAccess xlReadOnly
With EmailItem
.Subject = txtsubject
.Body = txtbody
.To = txtpersons
.Attachments.Add Wb.FullName
.Send
End With
Kill Wb.FullName
Wb.Close False

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End If
End Sub

v_gyku
09-09-2005, 02:24 AM
I have attched my project with this message can u pls check wats the problem?

sheeeng
09-09-2005, 04:39 AM
Seem interesthing....

Killian
09-09-2005, 05:27 AM
OK well it would appear this is a Visual Basic project which probably would have been worth mentioning.
I don't have VB6 (only v5 or .net) so I doubt I'll be able to use your project files - I guess it comes down to the error with the Excel specific code (when you post that you have an error, you should also detail what the error number and description is.) so here's a suggestion...


FileName = Sheets(List1).Text '& " - " & ActiveWorkbook.name
Since you've commented out the activeworkbook part, this code can produce a number of possible errors:
1. A Type Mismatch - The data type of the variable FileName isn't compatible with what your assigning to it.
2. Sheets(List1) - there isn't a worksheet with the name List1.Value - or theres and error with the List1.Value property
3. Sheets(List1).Text - Worksheets don't have a Text property - is it the worksheet name you want? If so Sheets(List1).Name should work. Although it looks like the name is already in the listbox (I'm guessing, I haven't seen the listbox code), so ActiveWorkbook.Sheets(List1) should also work

Justinlabenne
09-09-2005, 05:43 AM
This is in vb6 I believe, I cannot get the files you have attached upgraded to Vb.Net so testing is not an option here because I don't have vb6 either.

Norie
09-09-2005, 07:48 AM
Its the requirement
What do you mean?

v_gyku
09-11-2005, 10:45 PM
HI everyone.
I am stuck up with this code from last 3 days.
pls help me out...
I am attaching my project here.
Pls try to solve the error.
Error is coming on the click of 'send worksheet button'.

Killian
09-12-2005, 01:59 AM
Can you give some feedback, perhaps regarding my post (http://www.vbaexpress.com/forum/showpost.php?p=43154&postcount=8) ?
It's diffucult to solve a problem if you don't have any information on what's happening.

v_gyku
09-12-2005, 04:53 AM
I tried the line told by u
but it didn't worked.....

Killian
09-12-2005, 05:10 AM
Which line?
I posted a line of code and then made some comments about where the error could be.
Did you read them? If so, what are the variable and expression values and what is the error code & description. Providing this information will help someone help you.

v_gyku
09-12-2005, 05:15 AM
runtime error 1004

method 'Sheets' of object '_Global' failed

error is on the following line


fileName = Sheets(List1).Text & " - " & ActiveWorkbook.name

if i comment or uncomment the activeworkbook part givin same error.

Killian
09-12-2005, 06:42 AM
Yes, now we're getting somewhere...
As the message says, the "Sheets" method has failed. You should specify which object it applies to, so ActiveWorkbook.Sheets(... etc would be better.
If you're working from a Visual Basic project, you should really set an object variable to the Excel application and the active workbook to avoid this kind of thing.
Although, as I mentioned before, "Text" is not a property of an item in the Sheets collection, so you can expect another error.
Or do you mean Sheets.(List1.Text)? Does List1 contain sheet names?

Also, there will be a major problem here

Kill Wb.FullName
Wb.Close False

this attempts to delete a file you have open and referenced, then close it

v_gyku
09-12-2005, 10:54 PM
yes list box contains sheet names.

v_gyku
09-13-2005, 03:54 AM
Hi everyone!
I have tried some things in the code
and its working partly
its sending the attched sheet to the outbox in excel.
I want it to directly mail to the selected email id.

Its creating folder , AND create a temporary workbook with the file name ?<worksheet name>.xls? in the folder created WHICH IS CREATED.

i AM ATTACHING THE NEW CODE. PLS CHECK IT OUT. AND HELP ME OUT.

Norie
09-13-2005, 11:37 AM
vivek

What you have attached is a Visual Basic project.

v_gyku
09-13-2005, 09:31 PM
yes its a vb project (activex control)