Help with repeating macro's

[WIGG77]

Hello,

First post here so brief introduction.
My name is Dave and I work for a UK based company who design and manufacture bespoke hand built wheelchairs.

I am by no means an Excel god and have only really been using the program for around 6 months.

I set out to create a simple workbook to track the wheelchairs through the various stages of our manufacture with a simple tick box system and a click button to confirm the chair is ready for despatch. When the button is clicked an email is created and sent to the relevant recipients with certain cells from the sheet copied into the body of the mail. I use the workbook as a shared file between 2 people.

So far so good.

I use 1 sheet for each week and after adding the next 6 weeks or so, with 35-40 lines on each week the workbook gets somewhat clunky and slow. Each click button is assigned a separate macro and I guess this could be the route of my problem. If I use this workbook with other Excel documents open which link to MS Project, I often get the white screen crash or a complete system lockup. I patched together the VBA from other code people had posted here and there on the internet and adapted it to suit my needs.

As the code just repeats is there a way to streamline it but still retain the ability the copy the correct cells into the email body? Is there a way to copy the same cells but using th row the button is on?

Any help or advise would be greatly appreciated.

Dave

(I wasnt sure of the best way to upload the book so added a link to the file from my Dropbox)


https://dl.dropboxusercontent.com/u/13653846/Prod Progress.xlsb

 

[Michael M]

It would help if you told me the Drive and Filepath name ??

 

[Michael M]

I'll take a punt, as I'm may not be available tomorrow
Change the line in red to suit your needs regarding the folders.
Depending on where your file is, you may have to remove the last backslash AND / OR change the Drive letter from C:\
You obviously may or may not need that many sub forlders....but other than that the file name shouldn't change

Sub MYKL1()
Dim ans As Range
Set ans = Application.InputBox("Which Chair is ready for Despatch, please Select a cell in Column B", "Chair Selection", Type:=8)
ans.Select
If MsgBox("Do you wish to mark " & ans & " as ready for despatch?", vbYesNo) = vbNo Then Exit Sub
ActiveSheet.Range(ans.Offset(, -1), ans.Offset(, 2)).Select
   ActiveWorkbook.EnvelopeVisible = True
   With ActiveSheet.MailEnvelope
      .Introduction = "This wheelchair has passed final inspection and is cleared for delivery."
      .Item.To = ""
      .Item.CC = ""
      .Item.Subject = ans & " / " & ans.Offset(, 1) & " is ready for despatch"
      filestr = "C:\folder\folder\folder\folder\" & ans & ".pdf"
      .Item.Attachments.Add (filestr)
     '.Item.Send
   End With
End Sub

 

[WIGG77]

Ah sorry. I was still half asleep

Brilliant. Thank you

 

[Michael M]

pleasure...