vba to copy column data from excel to notepad and separate data with semi colons?

I was hoping for some assistance with a macro that would copy data in a particular column in excel and then copy it to notepad inserting semi colons between the data. Ideally I would like the data in notepad
to look like 1111111;1111112;11111113; etc instead in a list like (on top of one another) format; though if the first choice isn't possible then the list type would be my second choice.

I found this code which pulls the data into notepad in a list type format (my 2nd choice) but i would need it to include the semi colons.

Sub TestNotePad()
     '// The range to copy - written freehand so change as needed
     Range("P2:P100").Copy
     
     '// Start Notepad with focus
     Shell "notepad.exe", vbNormalFocus
     
     '// Send the standard CTRL+V. Pastes to the
     '// active window (Notepad, hopefully)
     SendKeys "^V"
     '// Back to the top of the file
     SendKeys "^{HOME}"
     
End Sub

Any help would be appreciated!

The way I would do this (which may not be what you need) is to export the data to a .txt file, rather than trying to shovel it into Notepad using SendKeys. Then you just open the file in Notepad. I can help with a code revision to do that, if you want.

If you need to put the data directly into the Notepad app then I'll have to drop out.

If you run Marc's code, then it will do basically what I suggested. It will create a file called "Export .txt" and put it in the same folder as your Excel file. The you can open that file with Notepad.

It uses a very clever method of extracting all the data in one line of code. It will start in P2 and extract the CurrentRegion. However, CurrentRegion will include other columns besides column P if there is data in the adjacent columns, and will cause a runtime error on the call to Join. So it should be tested with your actual data.