0

I'm building a automatic e-mail from VBA that takes a specific range. but now the macro is taking the specific range of an active sheet. I dont know how to specify my workbook and sheet properly.. maybe someone can help?

Sub Send()


Dim r As Range

Set r = Range("rm")

With r

ActiveWorkbook.EnvelopeVisible = True

With .Parent.MailEnvelope.Item
.To = ""
.CC = ""
.BCC = ""
.Subject = "Automatic Message: E-comm numbers Today"
.Send

End With

End With




End Sub

it should be in this workbook/sheet 

Workbooks("Copy of Copy of shipments 5.1.xlsm").Sheets("rapportages")

hope you can help :)!

vanleeuw
  • 27
  • 4
  • Is the workbook you need the range in always open when you run the macro? – DirtyDeffy Jun 14 '18 at 09:51
  • @DirtyDeffy, yes it is! but if i'm in another workbook then it sends the range of the workbook i'm in... – vanleeuw Jun 14 '18 at 09:56
  • Have a look at this link: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It covers the active sheet, active book, etc. – Darren Bartrup-Cook Jun 14 '18 at 10:09
  • replace `ActiveWorkbook` for `Workbooks("Copy of Copy of shipments 5.1.xlsm")` – Aneta Jun 14 '18 at 10:10
  • Hi @Aneta thanks for the tip but it still send the current worksheet that I'm in at that moment.. – vanleeuw Jun 14 '18 at 10:17
  • Maybe you will have to activate the workbook first to work with your range; maybe try to add a line like: Workbooks("Copy of Copy of shipments 5.1.xlsm").Activate before you set r and start your with. – JvdV Jun 14 '18 at 10:30

1 Answers1

1

Use fully qualified references:

Set r = Workbooks("Copy of Copy of shipments 5.1.xlsm"). _
    Sheets("rapportages").Range("rm")

When you don't do that Excel constructs the range object from the active workbook instead of the one you want in this case.

Miqi180
  • 1,670
  • 1
  • 18
  • 20
  • Hi Miqi even whit this changed it still send my current worksheet i'm working in.. Thanks for your time! – vanleeuw Jun 14 '18 at 10:19
  • @vanleeuw Something else is going on than in the code snippets you posted then. Please make an edit and post more relevant code below, if you want us to look into it. – Miqi180 Jun 14 '18 at 10:46