• 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.

Copying values to buffer and sending an email

MrJimi

New Member
Hello Experts!
I've been having a problem with creating mail sending macro from my report using MS Office 2010.

I have come up with an idea to put header of the report in separate sheet “buffer” and then create a macro which will copy row’s range underneath it and then send it all in msg body. It seems that it will make it less fragile.
I need to have AF column active in a main sheet. For example: When double-clicking on AF2 cell, Range("A2:AD2") should copy over to “buffer” sheet. It looks like:
Code:
Range("A2:AD2").Select
Selection.Copy
Sheets("buffer").Select
Range("A3").Select
ActiveSheet.Paste

This macro needs to be applied to each row.
Double-click on AE2 cell copy Range("A2:AD2"), And paste it to the same place A3 in “buffer” sheet and send email.
Double-click on AE3 cell copy Range("A3:AD3"), And paste it to the same place A3 in “buffer” sheet and send email.
Double-click on AE4 cell copy Range("A4:AD4"), And paste it to the same place A3 in “buffer” sheet and send email.
And so on…
It seems to be easy but don’t know how to make it possible.
Second part which is send email does not require code to be changed each time when double click is performed. That is why “buffer” has been created. This should make it easier.
I have managed to define recipients and email details using following:

Code:
DLsTo = wTarget.Worksheets("buffer").Cells(3, 29)
DLsCC = wTarget.Worksheets("buffer").Cells(3, 28)
Subjects = wTarget.Worksheets("buffer").Cells(3, 27)
Messagetext = wTarget.Worksheets("buffer").Cells(20, 2)
'Remove Formulas
wTarget.Worksheets("buffer").Range("A1:AD3").Copy
wTarget.Worksheets("buffer").Range("A1:AD3").PasteSpecial xlPasteValues


'Create Email
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = DLsTo
.CC = DLsCC
.Bcc = DLsBCC
.Subject = Subjects
.Body = Messagetext
I’ve been told that this forum is the best place to ask for help.
Experts, how to make first part applicable for each rows and how to merge those lines into smoothly working macro?
It is a hard nut to crack for me. I am learning every day something new about Excel VBA but the clock is ticking and I am asking for your help.
Thank you so much in advance!
MrJimi
 
Back
Top