• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Copy data for specific date from different worksheet but from same workbook

MaunishP

MaunishP

Member
Hi Team,
I am clubbing my previous thread here, due to Thread name i have posted incorrect, hence there were no views towards it.
http://chandoo.org/forum/threads/send-mail-from-excel-to-outlook.22962/

Here goes my query :

I have an excel file each month with three different worksheet. I need to send emails to my vendor at every bi-hour regarding today's work performed. I would like to have a VBA code which will select data by going into all 3 worksheets selecting today's data.

I have attached sample excel file which i require.

I have a code which will allow me to send emails using microsoft excel, now i would like to have above given query code to club with below given code >

Note to be taken :
Total 4 mails data should be send with different data
1st data should be taken from Mail 1 Worksheet for today's date
2nd data should be taken from Mail 2 Worksheet for today's date
3rd data should be taken from Mail 3 and 4 only for CCTV vendor for today's date
4th data should be taken from Mail 3 and 4 only for Tea and AC Vendor for today's date


Code: 
Sub Preview()

I = Cells(2, "B").Value  ' dynamising startrownumber to user fed value at cell B2
Do         ' start the action ,buddy!!!!
Subj = Cells(I, "A").Value
Filepath = Cells(I, "B").Value
EmailTo = Cells(I, "C").Value
CCto = Cells(I, "D").Value
msg = Cells(I, "E").Value

Application.DisplayAlerts = False' hey macro ,i dont wanna make you take time ,so this command to save time to avoid displays
Dim OutApp AsObject
   Dim OutMail AsObject
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
   OnErrorResumeNext
   With OutMail
        .To = EmailTo
        .CC = CCto
        .BCC = ""
        .Subject = Subj
        .body = msg
        .Attachments.Add Filepath
        .display
           
   EndWith
   OnErrorGoTo 0
   Set OutMail = Nothing
   Set OutApp = Nothing

Application.DisplayAlerts = True

I = I + 1

Cells(1, "A").Value = "Outlook sent Time,Dynamic msg preview  count  =" & I - 3

LoopUntil Cells(I, "C").Value = ""


EndSub

Regards,
Maunish Patel
 

Attachments

  • April 2015 - Vendor Management.xlsx
    432.5 KB · Views: 4
Question: do you want the email to attach a file with the desired data, or insert it into the body of the message? Your file gives the impression you want the data in the email body, not an attachment.
 
Hi Dianacris,
Yes, i want data inserted into email body not attachment. For attachment I have macro which i found from Chandoo Forum. Here it goes attached, however even this will not be useful.



Regards,
Maunish Patel
 

Attachments

  • Team Members.xlsm
    28.6 KB · Views: 11
You can use the email code you included in your second post, but you just take out the ".Attachments.Add Filepath" because you don't want the file attached. What you are trying to build is the ".body = msg" portion.

First, take all of the outlook code and put it in its own procedure. I'm attaching a file where I put the code in a SendEmail procedure so that you simply pass the values to it each time you need an email sent. Since the call is inside the loop, it will be executed 4 times.

Next you need code to gather the data you want in the body. Turn your data on the three "Mail" worksheets into tables. This will make things considerably easier, because then you can record a macro that filters each table. Then modify the "date" portion of the filter in the macro to a formula for today's date.

Once the table is filtered, write code to loop through the rows of data and append that information to the msg variable.
 

Attachments

  • April 2015 - Vendor Management.xlsm
    443.9 KB · Views: 5
Hi Dianacris,

I am okay if data is pasted in another sheet and then send it to outlook mail.

Could you please work on copying data for specific date range? Which i have asked at starting in this post. Once that is seen than we can have go for few altertation or modification.

Also i was not able to open macro. Error shown Capture.PNG
 
Hi Ninjas / Membes / Experts,

Can someone provide some lights on above given query.

Regards,
Maunish Patel
 
Hi All,
Almost, Dianacris has provided done 95% of task, now only challenge left is how to copy data for today's date and insert in column E.

Regards,
Maunish Patel
 
Hi Deepak,

I am not much good into VBA, however i am learning from my mistakes, could you please add to above sheet and so me how does it work.

Also will data be pasted for today date there? with the same macro?

Regards,
Maunish Patel
 
You must log in or register to reply here.
Back
Top