Logical problem

I am sending email using vb code. My code is working fine.
I have one difficulty...

Code:

---

Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    fileName = List1.Text  ' here i am setting the filename as selected worksheet from list.
    Debug.Print fileName
 
    SaveName = folderPath & "RightAnswersTempWorkbooks" & Num & "\" & StripChars(fileName, "[/,\\,*,?,"",<,>,|,:]", "")  'folderpath is the path where the original selected workbook is located.num is random number.
    ActiveSheet.Copy
    Set Wkb = ActiveWorkbook
    Wkb.SaveAs SaveName

 With EmailItem
        .Subject = txtsubject.Text
        .Body = txtmessage.Text
        .To = Txtname.Text
        .Attachments.Add Wkb.FullName ' wkb.fullname is complete path
        .send
    End With

---

What i am doing is .. user can select a workbook.All the sheets from that workbook will be listed in a listview.Now user can select a single sheet from listview. i am copying the selected worksheet in a new tempworkbook, and placing this workbook in new folder (folderPath & "RightAnswersTempWorkbooks" & Num) while sending mail i am attaching that temp workbook to mail.After sending the mail i am destroying the folder (folderPath & "RightAnswersTempWorkbooks" & Num)

I want my code to work if user selects more than one worksheet. i.e for each selected worksheet i will copy the sheet into new folder(folderPath & "RightAnswersTempWorkbooks" & Num). and while sending i will attached these files from folders as an attachment.

if the user selects multiple sheets just loop through the listview...

Activate the selected sheet one at a time.. run your code to copy save & move to the next selected sheet

Sorry i didn't mark thread as solved.

I got the solution. Actually i was confused in my code only. It's a really ....... one.

I loop in list box and its working properly.

Code:

---

Wkb.SaveAs SaveName
 
    On Error GoTo finderror
   
    With EmailItem
        .Subject = txtsubject.Text
        .Body = txtmessage.Text
        .To = Txtname.Text
        .Attachments.Add Wkb.FullName
        .send
    End With

---

Here i am attaching activeworkbook to the mail.
But with my new code, i have selected two worksheets and stored in a folder(both the sheets). How will i attached both the sheets?
Anyone knows how can i send multiple files as attachment?

Quote:

---

Originally Posted by v_gyku

Sorry i didn't mark thread as solved.

I got the solution. Actually i was confused in my code only. It's a really ....... one.

I loop in list box and its working properly.

---

For everyone else reading this, this is why it is kind of important that a thread get marked as resolved once an answer has been found. :)

Hey Hack my thread is not solve, i thought like it is solved but it is no solved...

Code:

---

Wkb.SaveAs SaveName
 
    On Error GoTo finderror
   
    With EmailItem
        .Subject = txtsubject.Text
        .Body = txtmessage.Text
        .To = Txtname.Text
        .Attachments.Add Wkb.FullName
        .send
    End With

---

I am attaching the activeworkbook to the mail.

But now i changed the code and i am selecting more than one sheet.
So how can i attach two sheets.(these are located in a same folder)
do i have to save it in array rather than storing in a string variable SaveName, if yes can u plz guide me on this....

Thanks...

The Attachments is a collection. So if your adding one then your done, but if your adding several to the same email then its needing a loop.

Ps, why not if they are selecting sheets to mail, add all the selected sheets to a single temp workbook. Then just attach the one workbook? Easier for the receipient.

Robdog thanks for posting...
I am helpless its client requirement.
Do u have any solution for 1st condition?

What is first condition? Just test if more then one item is selected in your listview then loop when adding each attachment.

thats not the problem i am copying each selected heet in a folder.
Now while sending the mail i have to take these sheets from folder and attched them.

When i was just sending a single sheet

[code]
Wkb.SaveAs SaveName

On Error GoTo finderror

With EmailItem
.Subject = txtsubject.Text
.Body = txtmessage.Text
.To = Txtname.Text
.Attachments.Add Wkb.FullName ' this is the path of temp workbook
.send
End With
[\code]

Since i was sending only one sheet
[code]
Attachments.Add Wkb.FullName
[\code]
it was working. But now it will take only last wkb...

Thanks...

Not sure if I understand the problem here. Can you explain a little more.

user has selected a single sheet from list.
Sheet will be copied to a temp workbook and will be placed in a folder.
After mail is sent this folder will be deleted.

I am doing same thing for more than one worksheet.
Two temp workbooks will be copied to same folder.

Now i want to attach these two tempworkbook in mail.

This will give u idea

Code:

---

Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    fileName = List1.Text  ' here i am setting the filename as selected worksheet from list.
    Debug.Print fileName
 
    SaveName = folderPath & "RightAnswersTempWorkbooks" & Num & "\" & StripChars(fileName, "[/,\\,*,?,"",<,>,|,:]", "")  'folderpath is the path where the original selected workbook is located.num is random number.
    ActiveSheet.Copy
    Set Wkb = ActiveWorkbook
    Wkb.SaveAs SaveName

 With EmailItem
        .Subject = txtsubject.Text
        .Body = txtmessage.Text
        .To = Txtname.Text
        .Attachments.Add Wkb.FullName ' wkb.fullname is complete path ' this is for active sheet.'i want to attch two tempworkbooks here.
        .send
    End With

---

Ok, so you check your List1 for multiple selections. Then save each out to your temp files. Create the email and do a loop around the .Attachments.Add adding each name as you loop. Then send.

thanks robdog

i wll try that tommorrow..
thanks for being with me.
Now i am leaving office...

Bye....

hello

I tried doing somthing like this...

Code:

---

With EmailItem
        .Subject = txtsubject.Text
        .Body = txtmessage.Text
        .To = Txtname.Text
        .Attachments.Add SaveName
        .Attachments.Add "c:\sample.txt"  ' I have also tried somthing like .attachments.add file1,file2
        .send
    End With

---

It dosn't work.
What i have to do to attch two files in a single mail?

You need to add them like so in a loop for ease of programming.

VB Code:

Dim arArray() As String
Dim i As Integer
'Populate your array with the filename/paths of the attachment file.
 
With EmailItem
    .Subject = txtsubject.Text
    .Body = txtmessage.Text
    .To = Txtname.Text
    For i = 0 to UBound(arArray) - 1
        .Attachments.Add arArray(i)
    Next
    .send
End With
Erase arArray