Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!

All Activity

This stream auto-updates     

  1. Earlier
  2. Hi, Recently I have Office 365 and also SharePoint with the new "Team Sites" environment (previously called workgroups). Anyhow, I have a macro which convert the excel sheet into pdf and puts this pdf in an email ready to send. This is the coding : sub sendPDF() Dim OutlookApp As Object Dim OutLookMailItem As Object Dim PdfFile As String, Title As String Dim myAttachments As Object ' Not sure for what the Title is Title = ActiveSheet.Range("D19") ' Define PDF filename PdfFile = ActiveWorkbook.FullName i = InStrRev(PdfFile, ".") If i > 1 Then PdfFile = Left(PdfFile, i - 1) ' PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf" dit is voor tabbladnaam erbij te geven PdfFile = PdfFile & "_concerning_" & ActiveSheet.Range("D19") & ".pdf" ' hiermee wordt de naam van persoon in bestand opgenomen ' Export activesheet as PDF With ActiveSheet .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False End With ' Use already open Outlook if possible On Error Resume Next Set OutlApp = GetObject(, "Outlook.Application") If Err Then Set OutlApp = CreateObject("Outlook.Application") IsCreated = True End If OutlApp.Visible = True On Error GoTo 0 ' Prepare e-mail with PDF attachment With OutlApp.CreateItem(0) ' Prepare e-mail .Subject = "MPR " & ActiveSheet.Range("H16") .To = "[email protected]" ' <-- Put email of the recipient here .Body = "Dear, " .Attachments.Add PdfFile .Display End With End Sub The excel file is located in a sharepoint team site, as it was previously in a workgroup. When running this macro, the e-mail is being prepared but when the pdf must be attached, Outlook will show the pdf as attachment, but underneath the pdfname there is notification of "Download failed" . Then I have to doubleclick it, then a notification comes up with the text, " you don't have the correct authorization" with two options "Again" and "Delete" . If I click again, then the pdf will be attached... :-( . How can I fix this ? Thank you ! Sapron75
  3. Hey everybody! I was wondering if anyone could point me in the right direction on how to use an Excel macro to communicate with an external application. In this particular case I am wanting excel to basically copy and paste information from completed cells to a mainframe application. One with multiple screens. For example, let's say I was needing to make 30 accounts using the mainframe, and each account has specific fields that need to be filled in (Acct #, various sales codes etc.); I would like to make a spreadsheet with labelled columns for each specific field that needs to be filled in, the user could input all the necessary information for all the accounts in the spreadsheet, press a Run Macro button, and have the process run in the background saving loads of time wasted on very manual data entry. I'm assuming VBA would work for this... I do know the application we are using to access our mainframe (Microfocus RUMBA+) has the ability to run it's own internal macros as well, and there are a few very basic interactions it can perform "right out of the box" so to speak with Microsoft products (exporting screen data to excel etc.). I know something like this is definitely possible as I've seen it in previous workplaces with similar applications. Could the starting place be as simple as Set IE = CreateObject ("Rumba.application")? I'm guessing there are some simple solutions and a couple more advanced ones, and pros & cons to both. Any help would be greatly appreciated! Thank you, -Dane
  4. No worries, give me a shout if you need any help with the rest of the suite! Caleeco
  5. Ah so simple! Thanks for your assistance. Now all I have to do is make the rest of the suite work and we'll be flying! Again, many thanks.
  6. Hi GeeperZ Thanks for sending over the file. I seem to have a website bug where the NOT (<>) symbol doesnt appear correctly in code samples. If you look at your TransposeData2 sub-routine, you'll see this line of code: Loop While rFound.Address = FirstAddress Change it to this: Loop While rFound.Address <> FirstAddress Tested on my end, seems to work. Let me know how you get on. Thanks Caleeco
  7. Hey Caleeco, thanks for getting back to me. I'll email the file through. I'll have to strip out some info first but other than that it will be the same as what I'm working on.
  8. Hey GeeperZ Post #2 should be the last 'working' code we made. Has the sheet structure changed at all? Are you able to email me the sheet you're working on so I can do some code testing? Thanks Caleeco
  9. Hey Caleeco, I'm being driven to resurrect this again. The project never got finished the first time around but as things have developed this has once again become of interest. Since I was last working on this I've had a new computer and I don't appear to have a working copy of the module. Any chance you could help? I've tried running the code as it appears on your forum but it does not work as expected, it gets to selecting the first instance and then Excel asks where I would like to paste the data. If I step into the macro and F5 it just returns the first result over again. I've looked on MrExcel too but no luck 😞
  1. Load more activity
  • Create New...